CODEWITHSUNDEEP
excelexcel-formulavba
Christian
Christian

Reputation: 23

Advanced Excel Sumif (may require VBA)

I have a list of grocery orders where I am trying to sum up the total avocado purchases only for grocery orders where limes are also purchased. I know this is possible with pivot tables and helper columns, but is there a clean, repeatable formula or VBA to solve this problem?

The spreadsheet below shows an example of what I am trying to do, and I want the green cells summed up ideally without creating a helper column as I need to repeat the process many times.

Grocery List Picture

Upvotes: 2

Views: 86

Answers (1)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=SUMPRODUCT(E5:E15*((B5:B15)="Avocado")*(ISNUMBER(MATCH((A5:A15)&"Lime",(A5:A15)&(B5:B15),0))))

Sums the range with totals and multplies by two Booleans (True=1,False=0). First is if the row is Avocado and the other is if the Bill # concatenated with Limes can be found using MATCH().

If both of these equate to true then you get VALUE * 1 * 1 to sum which is the result you want.

If either is false you instead get VALUE * 0 * 1 which will give 0 for either result.

enter image description here

Upvotes: 1

Related Questions