Thread: SUMIFS formula help Thanks: 0 Likes: 0

1. SUMIFS formula help

Hi,

Im having problems with my formula for my database see below

=SUM(SUMIFS(Data!AA\$7:AA102304,Data!D\$7:D102304,"*Prim*",Data!H\$7:H102304,">=3.01",Data!H\$7:H102304,"<=3.50",Data!S\$7:S102304,{"AmberH","GreenH"},Data!Z\$7:Z102304,{"AmberA","GreenA"}))

The returns i get from using the formula above do not match the returns when i manually filter the database to verify the return.

It works OK when there is multiple criteria and just one criteria in either the S and Z ,

=SUM(SUMIFS(Data!AF\$7:AF102303,Data!D\$7:D102303,"*Prim*",Data!H\$7:H102303,">=5.511",Data!H\$7:H102303,"<=6.00",Data!S\$7:S102303,{"AmberH","GreenH"},Data!Z\$7:Z102303,"*RedA*"))

=SUM(SUMIFS(Data!AE\$7:AE101788,Data!D\$7:D101788,"*Prim*",Data!H\$7:H101788,">=3.01",Data!H\$7:H101788,"<=3.50",Data!S\$7:S101788,"*RedH*",Data!Z\$7:Z101788,{"AmberA","GreenA"}))

but i cannot seem to get the formula to work when i need multiple criteria match in two columns. any help appreciated

2. Re: SUMIFS formula help

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("Prim",Data!D\$7:D10)))*(Data!H\$7:H10>=3.01)*(Data!H\$7:H10<=3.5)*((Data!S\$7:S10="AmberH")+(Data!S\$7:S10="GreenH"))*((Data!Z\$7:Z10="AmberA")+(Data!Z\$7:Z10="GreenA"))*(Data!AA\$7:AA10))

3. Re: SUMIFS formula help

Modify it as:

=SUM(SUMIFS(Data!AA\$7:AA102304,Data!D\$7:D102304,"*Prim*",Data!H\$7:H102304,">=3.01",Data!H\$7:H102304,"<=3.50",Data!S\$7:S102304,{"AmberH","GreenH"},Data!Z\$7:Z102304,{"AmberA";"GreenA"}))

Another option is:

=SUMPRODUCT(Data!AA\$7:AA102304,--ISNUMBER(SEARCH("prim",Data!D\$7:D102304)),--(Data!H\$7:H102304>=3.01),--(Data!H\$7:H102304<=3.50),--ISNUMBER(MATCH(Data!S\$7:S102304,{"AmberH","GreenH"},0)),--ISNUMBER(MATCH(Data!Z\$7:Z102304,{"AmberA","GreenA"},0)))

4. Re: SUMIFS formula help

=SUM(SUMIFS(Data!AA\$7:AA102304,Data!D\$7:D102304,"*Prim*",Data!H\$7:H102304,">=3.01",Data!H\$7:H102304,"<=3.50",Data!S\$7:S102304,{"AmberH","GreenH"},Data!Z\$7:Z102304,{"AmberA";"GreenA"}))

This one works perfectly, thankyou very much

5. Re: SUMIFS formula help

Originally Posted by juca73

=SUM(SUMIFS(Data!AA\$7:AA102304,Data!D\$7:D102304,"*Prim*",Data!H\$7:H102304,">=3.01",Data!H\$7:H102304,"<=3.50",Data!S\$7:S102304,{"AmberH","GreenH"},Data!Z\$7:Z102304,{"AmberA";"GreenA"}))

This one works perfectly, thankyou very much
Great. You are welcome.