sumif vs sumproduct question

[Jim]

Hi,

I have a simple spreadsheet with column B containing all dates, and column D
containing all numerical values. The dates range throughout a year. At the
bottom of the sheet I've entered January through December in another column.
To the immediate right of the month named, I have a formula which gives me
the total for that month. For instance, next to January my formula reads:
=sumproduct(--(month(b6:b370)=1),d6:d370) this formula works, but it seems
to me I should be able to use SUMIF too. I've tried
=sumif(d6:d370,month(b6:b370)=1,d6:d370) but that just returns a blank cell.
I know this is silly, but it's driving me nuts. Can someone explain what I'm
missing?

TIA,

Jim

 

[Volker]

Hi Jim,

You cannot apply functions to your test range with SUMIF.

With a helper column it would work:
K1: =month(b1)
copy down

=SUMIF(K1:K99,1,D199)

Same is true if you need to test 2 or more criteria. You would need a
helper column, too.

I suggest to use SUMPRODUCT only.

Regards,
Volker

 

[Arvi Laanemets]

Hi

=SUMIF(D6370,"<" & DATE(YEAR(TODAY()),1+1,1),D6370)-SUMIF(D6370,"<" &
DATE(YEAR(TODAY()),1,1),D6370)

sums all data from January of current year.

 

[Bob Phillips]

If you are going to use SUMPRODUCT to test for January a couple of things to
note.

Any blank cells pass the test, because they get treated as 0, 1st Jan 1900,
so they match the criteria. I tend to add a test to exclude blanks.

You might want to add a test for the year as well, if you can have multiple
year data.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jim]

Bob,

that's a good point; thank you. It's also a good way to catch typos.

jim