trying to use a lookup type function in SUMIFS

[dcfincham]

I am trying to use the SUMIFS function to extract summary information out of a file but would like one of the criteria to be if the value being examined exists in a data table column.

The following formula works for summing all values in E3 which equal apple (plus the rest of the criteria):-
=SUMIFS(F3:F2000,C3:C2000,"Africa",D3:D2000,2007,E3:E2000,"Apple")

The problem I have is that instead of defining the full formula again just to combine the values for column E values equal to "Pear", I would like to be able to refer to an array/list/table where I can define all the values for column E that I would like to combine... I have tried the following (amongst others) and it did not work but it highlights what I am trying to achieve:-
=SUMIFS(F3:F2000,C3:C2000,"Africa",D3:D2000,2007,E3:E2000,OR("Apple","Pear","Peach"))

 

[Colin Legg]

Here's a couple of options for you.

If you use an inline array constant you can use this formula:

=SUM(SUMIFS(F3:F2000,C3:C2000,"Africa",D3:D2000,2007,E3:E2000,{"Apple","Pear","Peach"}))

If you want to list the criteria in another range, say:
A1 contains Apple
A2 contains Pear
A3 contains Peach

Then you can use this formula:

=SUMPRODUCT(SUMIFS(F3:F2000,C3:C2000,"Africa",D3:D2000,2007,E3:E2000,A1:A3))

HTH,
Colin

 

[dcfincham]

Hi Colin

Thanks. Much appreciated. I tried to use the following which worked:-
=SUMPRODUCT(SUMIFS(F3:F22,C3:C22,"Africa",D3:D22,{2007,2008},E3:E22,I13:I15)) where I used the inline array feature for the second criteria (Year) and the range criteria for the fruit.

I then tried using a range criteria for the year selection (H13:H14) as well but this did not work for some reason... Any ideas why?

=SUMPRODUCT(SUMIFS(F3:F22,C3:C22,"Africa",D3:D22,H13:H14,E3:E22,I13:I15))

Dave

 

[Colin Legg]

I don't think that first example you posted will be calculating the correct result.

To make the second one work, you can use thism which is completed in the formula bar with CTRL+SHIFT+ENTER not just ENTER. This should put curly braces around the formula { }:

=SUM(SUMIFS(F3:F2000,
    C3:C2000,"Africa",
    D3:D2000,TRANSPOSE(H13:H14),
    E3:E2000,I3:I15))

But this is getting a little messy. Another option would be this:

=SUMPRODUCT(F3:F2000,
    --(C3:C2000="Africa"),
    --(ISNUMBER(MATCH(D3:D2000,H13:H14,0))),
    --(ISNUMBER(MATCH(E3:E2000,I3:I15,0))))

HTH,
Colin

 

[Colin Legg]

Dave, correction - that first formula you posted does produce the correct result.

Btw, for the date, if you were willing to use a horizontal range instead of a vertical range, you can adjust to:

=SUMPRODUCT(SUMIFS(F3:F2000,
    C3:C2000,"Africa",
    D3:D2000,G13:H13,
    E3:E2000,I3:I15))

HTH,
Colin

 

[dcfincham]

Thanks again Colin. You are a scholar and a gentleman...

I have tested it using the horizontal ranges and it works. Thanks again...

 

[dcfincham]

Hi Colin

The limitation in using this formula is that you can only have two range criteria used and then one must be defined as Vertical and the other as Horizontal. The order of which range is defined as Horizontal or vertical does not matter.

Cheers
Dave

 

[Colin Legg]

That's right, hence the TRANSPOSE() or using horizontal range. Which way around might matter if you have a long list and you run out of columns.

If you wanted, say, to also expand your continents beyond Africa, you would have to revert back to the ISNUMBER/MATCH construct I mentioned:

=SUMPRODUCT(F3:F2000,
    --(ISNUMBER(MATCH(C3:C2000,G13:G14,0))),
    --(ISNUMBER(MATCH(D3:D2000,H13:H14,0))),
    --(ISNUMBER(MATCH(E3:E2000,I3:I15,0))))