Using Date Range with Sumif - SOLVED

English
, ,
#1

Hello,

I have a sheet set up to do general journal entries for accounting. I wish to sum the amounts for each account according to the month of the transaction.

Currently, my sheet has the date in column B, beginning at row 8 (B8), and is allowed to extend to the bottom of the sheet (B:B).

The account codes (501, 502, 503, etc.) are entered into column F, beginning at row 8 (F8), and are allowed to extend to the bottom of the sheet (F:F).

The debits for each entry are in column H and the credits are in column I.

I have experimented with the SUMIF function, attempting to merely add the debits, without worrying about the credits, using the following formula for the first month of the year:

=SUMIF(B8:B1000,MONTH(1),H8:H1000)

This gave zero as a result.

Can anyone point out my error in this, and possibly help with my long term goal?

Many thanks,
Kirk

#2

I think help will come faster if you upload a sample file. :slight_smile:

#5

I have tried each of the solutions presented and they did not work. I am uploading my worksheet as suggested. I was entering the formula in cell M8 as a test. A later learning exercise will be to sort on a second criteria (the ACCT #) which is why I started my learning using the SUMIF (or SUMIFS) function.
Cash Book- example.ods (85.1 KB)

#8

How dates in spreadsheets work, and why Calc uses 1899-12-30 as day zero: https://ask.libreoffice.org/uploads/short-url/csJPcPDTV9RgwGbG0VvDciFFH0O.ods and why MONTH(1) returns 12.

Pivot table grouped by months and years: https://ask.libreoffice.org/uploads/short-url/m4CfCqn8ejQTSnyjsRjrJ48LYir.ods

#4

MONTH(1) returns 12 because the serial day number 1 is 1899-12-31 (null date is 1899-12-30) so that never matches any first month in any year, whatever values you may have in B8:B1000. It would match a numeric value 12 though.

You can use either

=SUMIF(MONTH(B8:B1000);1;H8:H1000)

entered as array formula (close with Shift+Ctrl+Enter instead of just Enter), or

=SUMPRODUCT(MONTH(B8:B1000)=1;H8:H1000)

or the expression suggested by karolus.

1 Like
#7

I have also tried the following, to no avail. I get a zero

#3

Hallo

=SUMIFS($H$8:$H$1000 ; 1 ; MONTH($B$8:$B$1000))

but anyway you should prefer to use ⇒ Data ⇒ Pivottable ⇒ create… instead juggling with formulas

1 Like
#6

=SUMIFS($H$8:$H$1000 ;MONTH($B$8:$B$1000);1) Ctrl+Shift+Enter

1 Like
#9

returns a #VALUE ! error. I like the solution because I understand it, but if it has to be entered as an array, I have to copy/paste and I do not know how with that.

I have gotten

to work, but am concerned that it will not work when I add a second criteria.

#10

Maybe there is an easy way, but I share what I tested.
I don’t know in which cell do you want the formula. I put it in A8.
If do you need the formula for the FEB sheet, just copy A8 (from JAN sheet), go to FEB sheet, paste in cell A8, edit, replace 1 with 2, and Ctrl+Shift+Enter.

Or, you can add a cell reference to the formula with the month number value in another cell (A7 in my sample), and then you can just copy and paste in other sheets.
imagen

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.