Sumif, using 4 conditions

Posted by Michelle on August 29, 2001 5:44 AM

I have a large table, where I would like to use the Sumif command.

Usually Sumif checks for 1 condition and then sums, I would instead like it to check for 4 true conditions and then sum the necessary info.
ie =sumif(a1:d2000;"t" and "s"and"d" and"r";g1:g2000)

where t,s,d,r are valus in separate columns.

Posted by Robert Jackson on August 29, 2001 6:26 AM

You will either need another column with
=AND(A1,B1,C1,D1)
or whatever the cells/references are and then sumif against that OR use the DSUM function.

Rob

Posted by Aladin Akyurek on August 29, 2001 6:45 AM

Are you saying that if any value in A1:D2000 is equal to "t" or "s" or "d" or "r", the corresponding value in G1:G2000 must be summed?

Or is it: Sum a value in G1:G2000 if other columns (you didn't say which) contain a "t", a "s", a "d", and an "r" at the same time?

Aladin

Posted by Raymond on August 29, 2001 6:48 AM

Array formula (CSE) :-
=SUM((A1:A2000="t")*(B1:B2000="s")*(C1:C2000="d")*(D1:D2000="r")*(G1:G2000))

Posted by Michelle on August 29, 2001 10:37 PM

I mean that if in a1:a2000, if any rows contain a "t", a "s", a "d", and an "r" at the same time.
Than sum the corresponding values in column g1:g2000

Posted by Aladin Akyurek on August 30, 2001 12:36 AM

Michelle,

I think it's

=SUMPRODUCT((A1:A2000={"t","s","s","r"})*(G1:G2000))

If not, post 5 rows of the relevant data.

Aladin

Posted by Michelle on August 30, 2001 3:26 AM

Thanks - solved with CSE formula!!

Hi Guys thanks for all the help. I solved the problem using the sum CSE formula from Raymond