Question about SUMIF

[Carl Imthurn]

Hi folks --

I haven't been able to figure out the SUMIF syntax for my problem, so
it's time to swallow my pride and get some help . . .

I have dollar amounts in cells D2 through D38.
I have either the letter C or the letter D or some other value in cells
E2 through E38

Here's what I want to do:
Sum cells D2 through D38 into D39
IF AND ONLY IF
the corresponding cell in column E does *not* have a C or a D in it

Here's what I've come up with so far for the formula in cell D39:

=SUMIF(E2:E38,NOT(OR('C','D')),D238)

but it returns 0 every time.

Any help will be very much appreciated - thanks in advance.

Carl

 

[JulieD]

Hi Carl

try using the SUMPRODUCT function - its designed for multiple criteria
=SUMPRODUCT((E2:E38<>"C")*(E2:E38<>"D")*D238)

for details on how it works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Cheers
JulieD

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(E2:E38<>"C"),--(E2:E38<>"D"),D238)

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Carl Imthurn]

Thanks Julie - that worked perfectly!
I'll have to read up on SUMPRODUCT to understand how it works.
Thanks again for getting back to me so quickly - very much appreciated.

Carl

 

[JulieD]

Hi Carl

glad it works and thanks for the feedback.

SUMPRODUCT is a very useful formula and if you do a search of the newsgroup
archives (i use groups.google.com) you'll find a variety of examples to
study.

Cheers
JulieD

 

[Carl Imthurn]

Thanks for your reply Frank -- looking at the slight difference between
your reply and JulieD's, it seems that there's more than one way to use
SUMPRODUCT.
I'll need to figure out how the '--' syntax works.
Thanks again for your ongoing help in this newsgroup.

Carl

 

[Frank Kabel]

Hi
yes indeed there're several syntax versions
Just follow the link Julie and i provided and you'll find an
explanation about this on thisn page