SUMIFS with mulitple criteria

[Journeyman3000]

Hi All

So excel seems to permit multiple criteria in a SUMIFS formula - '=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))

I am looking for a way to reference a cell rather than hard define the values e.g. '=SUM(SUMIFS(sum_range,criteria_range,{A4,A5}))

For example:
1 R 10
2 R 10
3 G 20
4 G 20
5 B 30
6 B 30

Actual Value
=SUMIFS(C42:C47,B42:B47,{"R","B"})) - This works

Reference Value
=SUM(SUMIFS(C42:C47,B42:B47,{$E$41,$F$41})) - This doesn't work.

Reference Value
=SUMIFS(C42:C47,B42:B47,$E$41:$F$41) - This returns on the R value (20)

Expected value = 80


Ideally I want to be able to filter on several criteria lists, i.e. Return a sum from several filtered columns using a single formula. is that even possible?

For example (Values | List One, List Two, List Three)

Where I could select the items in each list and it would ultimately return the complete summed value of those selections. I could do this using VBA, but if there's a better way using formulas, I'd like to work with that instead.


Thanks for helps

[bebo021999]

Basically:
{X,Y} apply for values only
If reference:
(E41:F41)
SUMIFS(C42:C47,B42:B47,$E$41:$F$41) establish 2 outcome like: {100,200} with 100 is sumif of E41 and 200 of F41
To sumup, try put in to SUMPRODUCT;

=SUMPRODUCT(SUMIFS...)

[Journeyman3000]

Thanks for the response.

This is close, but not quite what I'm after.

I did this: =SUMPRODUCT(SUMIFS(E2:E28,C2:C28,FindList,D2:D28,FruitList)) and SUMIFS(E2:E28,C2:C28,FindList,D2:D28,FruitList)

Both of these work wherever I do not have a unique list and am matching items directly. E.g.

Data Table
Milk Apples $100.00
Milk Orange $100.00
Milk Pears $100.00
Cookies Apples $100.00
Milk Apples $100.00


Result
Milk Apples $200.00
Milk Orange $100.00
Milk Pears $100.00
Cookies Apples $100.00

Formula
Milk | Apples =SUMIFS(E2:E28,C2:C28,FindList,D2:D28,FruitList) = $200

This formula is correct - however, to make it work I need to have two lists with every combination of possible matches.

I need a single formula that will read a unique List One and unique List Two and tell me the total wherever a match between the two lists is found. So that:

I need to match only 2 unique Lists.

List One List Two Result
Milk Apples $200
Cookies Pears ---
Produce Oranges ---

I tried =SUMIFS(E2:E28,C2:C28,FindList,D2:D28,FruitList), but this only showed direct match total - where I had every combination matching in two columns. That would be useful but not in this scenario.

Hope that's a little clearer, and thanks for helping.

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Journeyman3000]

I've included an attachment.

I just need a way to collate the data such that it it calculated based on the selections I've made.

I hope this attachment helps to get it.

Cheers

[AliGW]

Given that the SUMIFS is giving the result you want, do I take it that this is an aesthetic question to do with accommodating merged cells?

If so, see here: https://excel.solutions/2016/10/usin...ctively-excel/