SUMIFS formula help

[juca73]

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

 

[DanteAmor]

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))

 

[Aladin Akyurek]

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)))​

_{^{<strike>
</strike>}}​

 

[juca73]

Aladin

=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"[FONT=&quot];[/FONT]"GreenA"}))

This one works perfectly, thankyou very much

 

[Aladin Akyurek]

Aladin

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