Summing a column with two intersecting conditions?

**curiousexcel** · 08-26-2008, 01:21 AM

I have a excel spread sheet where I wish to sum a column based on the outcome of two other columns.

A B C
5 Normal High
10 Abnormal High
15 Normal Low

Now if I wish to sum those numbers in column A which are Normal (for B) and Low (For C) then how would I go about doing that. I was attempting to use an array formula but was having many issues. So far I have this...

=SUM(IF($M$16:$M$838="Normal", IF($R$16:$R$838="Interval 1", $N$16:$N$838,0),0))

But the number it gives me I know is wrong, it ends up summing everything in the column rather then the selected ones.

The variables are different but I hope the message got across.

**arthurbr** · 08-26-2008, 01:58 AM

Hi and welcome to the board
You may try
=SUMPRODUCT(($M$16:$M$838="Normal")*($R$16:$R$838="Interval 1") *$N$16:$N$838)

HTH

**curiousexcel** · 08-26-2008, 09:14 AM

Thank you that worked great.

I was wondering if I wanted to make the second condition a time variable, ie add those numbers between a certain time.

I had come up with the following, however it is adding all numbers that which are normal and not the ones between those specific times. Do I have the time part of the equation correct? I actually needed it for 3 different time period, 6am-6pm, 6pm-12am, and 12am-6am. Any help would be greatly appreciated.

=SUMPRODUCT(($M$16:$M$838="Normal")*(PRODUCT({"06:00","18:00"}-E16)<0)*$N$16:$N$838)

=SUMPRODUCT(($M$16:$M$838="Normal")*(PRODUCT({"18:01","0:00"}-E16)<0)*$N$16:$N$838)

=SUMPRODUCT(($M$16:$M$838="Normal")*(PRODUCT({"0:01","5:59"}-E16)<0)*$N$16:$N$838)