if and sum, 2nd try.

[Christy]

This is what I have:
Col G Col H Col I
15 in v
20 ot v
30 in n
This is what I need to do:
When I13:I36 = v AND H13:H36 = in, THEN add G13:G36
Thanks for your help
Christy

 

[Don Guillett]

=sumproduct((I13:I36 ="v")*(H13:H36 ="in")*G13:G36)

 

[Pete_UK]

You can do it like this:

=SUMPRODUCT((I13:I36="v")*(H13:H36="in")*(G13:G36))

Hope this helps.

Pete

 

[JR]

Ok, I am totally confussed. I am trying to do the same thing. I have a
column of dates, and another column of {High, Medium, Low, Critical}. I am
trying to use SUMPRODUCT to count the number of rows that have Critical on
9/17/2008. Even when I try this:

=SUMPRODUCT(B1:B12="Critical")

I get 0, and there should be 4. So no way can I get this to work:

=SUMPRODUCT((A1:A12="9/17/2008")*(B1:B12="Critical"))

What am I messing up??????

Thanks,
JR

 

[JR]

Ah I think my problem is the date column. How do I select dates using
SUMPRODUCT?

 

[Pete_UK]

You could put the date of interest in a cell somewhere, eg D1, then
you could do this:

=SUMPRODUCT((A1:A12=D1)*(B1:B12="Critical"))

Or, if you want to stick with your own approach, you would need to do
it like this:

=SUMPRODUCT((A1:A12=--"9/17/2008")*(B1:B12="Critical"))

The -- converts the text date to a proper date. Another way is:

=SUMPRODUCT((A1:A12=DATE(2008,9,17))*(B1:B12="Critical"))

The drawback with these last two is that the date of interest is coded
within the formula, so you would have to amend the formula if you
wanted to know the result for a different date, whereas with the first
formula you can just change the date in D1. You could also put the
other criteria in a different cell in the same way.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

You may also try the following:

=sum(if((range3="v")*(range2="in"),sum_range))

Please press Ctrl+Shift+Enter after the formula.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com