Nesting "sumif" function in Excel problem

  roger 11:34 14 Mar 2003
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 2003

=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 2003

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 2003

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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

Halloween Photoshop & Illustrator tutorials: 20 step-by-step guides to creating spook-tacular…

iPhone X news: Release date, price, new features & specs

Comment créer, modifier et réinitialiser un compte Apple ?