SUMIFS formula help

juca73

New Member
Joined
Dec 30, 2017
Messages
40
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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))
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top