sumif vs sumproduct question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 22 Dec 2006 03:59 GMT

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

Signature

"The trouble with the world is that the stupid are cocksure and the
intelligent are full of doubt." --Bertrand Russell

Reply to this Message

Volker - 22 Dec 2006 06:14 GMT

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,D1:D99)

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

Reply to this Message

Arvi Laanemets - 22 Dec 2006 08:40 GMT

Hi

=SUMIF(D6:D370,"<" & DATE(YEAR(TODAY()),1+1,1),D6:D370)-SUMIF(D6:D370,"<" &
DATE(YEAR(TODAY()),1,1),D6:D370)

sums all data from January of current year.

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> Hi,
>
[quoted text clipped - 12 lines]
>
> Jim

Reply to this Message

Bob Phillips - 23 Dec 2006 10:18 GMT

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.

Signature

HTH

Bob Phillips

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

> Hi,
>
[quoted text clipped - 12 lines]
>
> Jim

Reply to this Message

Jim - 26 Dec 2006 20:25 GMT

Bob,

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

jim

> If you are going to use SUMPRODUCT to test for January a couple of things
> to
[quoted text clipped - 32 lines]
>>
>> Jim

Reply to this Message

Rate this thread: