SUMIFS with 2 criteria in same column

[tdbarber]

The attached spreadsheet has the data I use. I highlighted one of the criteria in yellow and the other in green. The manufacturers are in yellow and stores in green. I need column B in the stores tab to reflect the number in Column I of the data tab based on the store number in column A of the Stores tab. I would prefer to have Excel lookup multiple instances of the manufacturers in yellow based on the single store number. There will normally be thousands of lines in the data tab. I.E. store 3001 is in the data tab 3 times, in green, with a total of 8 units in column I under 3 different manufacturers. 3 under '0, 1 under 'ABDG and 4 under ACC.

[AliGW]

Please manually fill in the results you are hoping for in the results sheet.

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

..another way if you tidied up the Data a little removing blank and total rows would be a Pivot Table.

[tdbarber]

Here you go

[Glenn Kennedy]

Richard's approach seems to work fine...

[tdbarber]

That only works for the total of all items under that store number. I am trying to obtain the number of units per manufacturer. I.E. the yellow highlighted numbers are the manufacturers and there might be a store number under each of those manufacturers. I want to obtain the totals of specific manufacturers for each location and not all for the same location. Let's say 3001 had 2 under 2 and 4 under ABDG, I would want to retrieve each of those amounts separately.

[Glenn Kennedy]

I don't follow you. Please amend your sheet to be truly representative ... and show expected answers, too...

[tdbarber]

I inserted another tab to show as an example

[BMV]

Array Formula

Please Login or Register  to view this content.

But for my opinion the simple helper column more usefull. For k2

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[tdbarber]

This works just great! Thanks for your help!