Nesting "sumif" function in Excel problem

  roger 11:34 14 Mar 03
Locked

I cannot get this combined formulae to work for me.

In column range c1:c10 each of the cells has a value of 100. The total in c11 is therefore 1000.

If I want to have the sum of only those cells in c1:c10 where the corresponding cell in a1:a10 contains the text value "s" and there are 5 with "s" the total in c11 will be 500.

The formulae =sumif(a1:a10,"s",c1:c10) works.

Also if make the formulae read =sumif(b1:b10,"1",c1:c10) this also works.

But I cannot combine them to calulate that if there are the five "s"'s in a1:a10 and only 3 "1"'s in the corresponding cells in the range b1:b10 the total in c11 is 300.

CAn anyone help please.

Rogie

  VoG™ 11:40 14 Mar 03

=SUMPRODUCT((A1:A10="s")*(C1:C10=100))

will give the answer 5 which you can multiply by 100 to get the answer that you are after.

Not sure if this helps.

  VoG™ 11:45 14 Mar 03

Didn't read the question properly

=SUMPRODUCT((A1:A10="s")*(C1:C10=100)*(B1:B10=1))

will give 3, etc.

  cherria 12:53 14 Mar 03

I guess there may be a reason you want to do it all in one, but how about putting in d1 the formula

=IF(AND(a1="s",b1=1),c1,"")

then simply summing column d. I think this accomplishes the end result but you may have a reason for not wanting anything in D.

This thread is now locked and can not be replied to.

What is Google Allo? What is Google Duo? Google Allo UK release date and features: Google Allo is…

1995-2015: How technology has changed the world in 20 years

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…