CODEWITHSUNDEEP
excelexcel-formula
user238801
user238801

Reputation:

SUMIF of multiple columns with INDIRECT

I have a Excel sheet which is used as database, let's call that MyDB in the following Example. The first column A consists of some strings.

A                | B            | C 
-----------------|--------------|------------------------------------------
Turnover 2014    | 1            | 2
Something        | 2            | 0
Something        |              | 
Turnover 2014    | 3            | 1
Something        |              | 
Something        | 0            | 2

What I want to do is look for the string Turnover 2014 and sum all values in that row from B:C (C is just an example in my case it will be variable and can be F or M).

What I have:

=SUMIF(INDIRECT("'MyDB'!A"&Helper!D2&":"&"A"&Helper!D8),"=Turnover 2014",INDIRECT("'MyDB'!$B"&Helper!D2&":"&"B"&Helper!D8))

The Helper!D2 and Helper!D8 contain the variable range, which is one of the reasons I have to use INDIRECT. For this example lets assume D2 = 1 and D8 = 6 (the full table)

Simple version:

=SUMIF(INDIRECT("'MyDB'!A1:A6"),"=Turnover 2014",INDIRECT("'MyDB'!B1:B6"))

This sums all values in B where A = Turnover 2014, so no problem here. Now I will show you my attempts to do the same with multi-columns:

=SUMIF(INDIRECT("'MyDB'!A1:A6"),"=Turnover 2014",INDIRECT("'MyDB'!B1:C6"))

=SUMPRODUCT((INDIRECT("'MyDB'!A1:A6") = "Turnover 2014")*(INDIRECT("'MyDB'!B1:C6")))

Both didn't work in my case (IMPORTANT I'm not talking about the simplified version I'm talking about the original version with all the variables).

In all cases I only get the sum of 4 where I need 7

Upvotes: 0

Views: 1445

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Check whether your column A contains Turnover 2014 without leading/trailing spaces.

And try:

=SUMPRODUCT(
 (TRIM(INDIRECT("'MyDB'!A"&Helper!D2&":"&"A"&Helper!D8)) = "Turnover 2014")*
 (INDIRECT("'MyDB'!B"&Helper!D2&":"&"C"&Helper!D8))
)

also I suggest you to take a look at alternative formula without INDIRECT which is much better because it's not volatile formula: 

=SUMPRODUCT(
 (TRIM(INDEX(MyDB!$A:$A,Helper!D2):INDEX(MyDB!$A:$A,Helper!D8))="Turnover 2014")*
 (INDEX(MyDB!$B:$B,Helper!D2):INDEX(MyDB!$C:$C,Helper!D8))
)

Upvotes: 1

Related Questions