CODEWITHSUNDEEP
excel
flyingmeatball
flyingmeatball

Reputation: 7997

excel sumifs with lookup value

I need to do a sumif with two conditions - one of which is probably going to be an array calculation. The formula in cells C3:E8 should be the sum of the corresponding column from I:k, where the field (column B) matches column G, for all countries that match the category in the given month. For example, C3 would be the sum of I3:I26, where G3:g26 == B3 and Lookup of H3:H26 in N3:N10 == A3.

Group1                      Group2                      Group3          
Category    Field   Jan Feb Mar     Field   Country Jan Feb Mar     Country Jan Feb Mar
Category1   Field1                  Field1  CountryA    5   10  3       CountryA    Category1   Category2   Category2
Category1   Field2                  Field1  CountryB    23  28  21      CountryB    Category2   Category2   Category1
Category1   Field3                  Field1  CountryC    17  22  15      CountryC    Category2   Category2   Category1
Category2   Field1                  Field1  CountryD    52  57  50      CountryD    Category1   Category2   Category2
Category2   Field2                  Field1  CountryE    18  23  16      CountryE    Category1   Category2   Category1
Category2   Field3                  Field1  CountryF    29  34  27      CountryF    Category2   Category2   Category1
                                    Field1  CountryG    6   11  4       CountryG    Category2   Category1   Category1
                                    Field1  CountryH    20  25  18      CountryH    Category1   Category2   Category2
                                    Field2  CountryA    5   10  3                   
                                    Field2  CountryB    23  28  21                  
                                    Field2  CountryC    17  22  15                  
                                    Field2  CountryD    52  57  50                  
                                    Field2  CountryE    18  23  16                  
                                    Field2  CountryF    29  34  27                  
                                    Field2  CountryG    6   11  4                   
                                    Field2  CountryH    20  25  18                  
                                    Field3  CountryA    5   10  3                   
                                    Field3  CountryB    23  28  21                  
                                    Field3  CountryC    17  22  15                  
                                    Field3  CountryD    52  57  50                  
                                    Field3  CountryE    18  23  16                  
                                    Field3  CountryF    29  34  27                  
                                    Field3  CountryG    6   11  4                   
                                    Field3  CountryH    20  25  18

enter image description here

The formula in C3 should be something like:

=sumifs(I:I,G:G,B3,*Arrayformulalookup?*,A3)

Any thoughts on how to proceed here?

Upvotes: 3

Views: 337

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

Here is the formula.

{=SUM(SUMIFS(I$2:I$25,$H$2:$H$25,IF($A2=N$2:N$9,$M$2:$M$9),$G$2:$G$25,$B2))}

you will get the result. Don't forget to use keystrokes CTRL + SHIFT + ENTER for Array formula.

Upvotes: 3

Related Questions