Multiple Criteria Sumifs in Multiple Columns

[chowyg]

My table is below. I want to basically do a sum if with multiple criteria, but multiple criteria in multiple columns. I know how to do multiple criteria in a single column which is done like this "=SUM(SUMIFS(C2:C17,A2:A17,{"Europe","USA"},B2:B17,"Alpha"))"... but I'm trying to take it one step further and add in "Bravo" as well.

If this were a math function I want to do basically this. (X+Y)*(A+B), which is four functions. X*A, X*B, Y*A and Y*B. I need a formula that can sum the results using the multiple criteria. (Europe+USA)*(Alpha+Bravo).

Sum the criteria (Europe*Alpha, Europe* Bravo, USA*Alpha, USA*Bravo).

I been working on this for some time, it's quite stressful. I know the geniuses on here can help me resolve this. If this is confusing, please let me know and I will try to simply my needs better. Thanks in advance!



[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Team[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Alpha[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Bravo[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Alpha[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Bravo[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Alpha[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Bravo[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Alpha[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Bravo[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]

 

[fdanwar]

Thanks everyone on this thread. I had to break down a worksheet with multiple pivot tables because the Xcelsius dashboard I was building on top of it does not allow refresh of pivot tables. I did this by using the following formulae:

1. SUMIFS for pivot tables with single values in the pivot filters
2. SUM(SUMIFS) with a "," separator for pivot tables with multiple values in one pivot filter
3. SUM(SUMIFS) with a "," separator and a ";" separator for pivot tables with multiple values in two pivot filters
4. SUM(SUMIFS,SUMIFS) for a pivot tables with multiple values in three pivot filters

I tried using SUMPRODUCT in the last case but could not figure out how to make it work and since time was short, adopted the slightly more cumbersome approach above. Which leads me to my question for the pros here: Can you please simplify the following formula into a SUMPRODUCT? I am using Excel 2013:

=SUM
(
SUMIFS(Metric,Filter1,{"Item1.1","Item1.2"},Filter2,{"Item2.1";"Item2.2"},Filter3,"Item3.1"),
SUMIFS(Metric,Filter1,{"Item1.1","Item1.2"},Filter2,{"Item2.1";"Item2.2"},Filter3,"Item3.2")
)

 

[Aladin Akyurek]

Thanks everyone on this thread. I had to break down a worksheet with multiple pivot tables because the Xcelsius dashboard I was building on top of it does not allow refresh of pivot tables. I did this by using the following formulae:

1. SUMIFS for pivot tables with single values in the pivot filters
2. SUM(SUMIFS) with a "," separator for pivot tables with multiple values in one pivot filter
3. SUM(SUMIFS) with a "," separator and a ";" separator for pivot tables with multiple values in two pivot filters
4. SUM(SUMIFS,SUMIFS) for a pivot tables with multiple values in three pivot filters

I tried using SUMPRODUCT in the last case but could not figure out how to make it work and since time was short, adopted the slightly more cumbersome approach above. Which leads me to my question for the pros here: Can you please simplify the following formula into a SUMPRODUCT? I am using Excel 2013:

=SUM
(
SUMIFS(Metric,Filter1,{"Item1.1","Item1.2"},Filter2,{"Item2.1";"Item2.2"},Filter3,"Item3.1"),
SUMIFS(Metric,Filter1,{"Item1.1","Item1.2"},Filter2,{"Item2.1";"Item2.2"},Filter3,"Item3.2")
)

Try...

=SUMPRODUCT(
       Metric,
       --ISNUMBER(MATCH(Filter1,{"Item1.1","Item1.2"},0)),
       --ISNUMBER(MATCH(Filter2,{"Item2.1","Item2.2"},0)),
       --ISNUMBER(MATCH(Filter3,{"Item3.1","Item3.2"},0)))

 

[rafuk73]

Hi All,

I have another question. I have the following formula which does the job:

=SUM(SUMIFS(Actual!$F:$F, Actual!$B:$B, $B6,
Actual!$C:$C, {"12/10/2015";"13/10/2015";"14/10/2015";"15/10/2015";"16/10/2015"},
Actual!$I:$I, {"Early","Late","Unscheduled","Unknown"}))

My question is how can I replace above dates with cell references, for example, A1, B1, C1 etc. When I tried entering in second row of the above formula the following
Actual!$C:$C, {A1; B2; C1; D1; E1}, nothing really worked.

Could you please help

Thanks!

 

[Aladin Akyurek]

Hi All,

I have another question. I have the following formula which does the job:

=SUM(SUMIFS(Actual!$F:$F, Actual!$B:$B, $B6,
Actual!$C:$C, {"12/10/2015";"13/10/2015";"14/10/2015";"15/10/2015";"16/10/2015"},
Actual!$I:$I, {"Early","Late","Unscheduled","Unknown"}))

My question is how can I replace above dates with cell references, for example, A1, B1, C1 etc. When I tried entering in second row of the above formula the following
Actual!$C:$C, {A1; B2; C1; D1; E1}, nothing really worked.

Could you please help

Thanks!

Try...

=SUMPRODUCT(SUMIFS(Actual!$F:$F, Actual!$B:$B, $B6,
Actual!$C:$C, A1:E1,Actual!$I:$I, {"Early","Late","Unscheduled","Unknown"}))

 

[rafuk73]

Try...

=SUMPRODUCT(SUMIFS(Actual!$F:$F, Actual!$B:$B, $B6,
Actual!$C:$C, A1:E1,Actual!$I:$I, {"Early","Late","Unscheduled","Unknown"}))

If I have only one cell reference in the formula (either A1 or B1 etc) it works. But when I insert A1:E1 the formula shows some result, but not the total for all those days in A1:E1. The amount was even lower than for any of those days counted separately..

 

[fdanwar]

I know this is a workaround, but you could try converting your dates to text in ddmmyyyy format using =TEXT(DAY(D1),"00")&TEXT(MONTH(D1),"00")&YEAR(D1) as an example. You can then use the SUMIFS range and it should work.

 

[Aladin Akyurek]

If I have only one cell reference in the formula (either A1 or B1 etc) it works. But when I insert A1:E1 the formula shows some result, but not the total for all those days in A1:E1. The amount was even lower than for any of those days counted separately..

My bad. We must adjust the other or-term accordingly...

=SUMPRODUCT(SUMIFS(Actual!$F:$F, Actual!$B:$B, $B6,
Actual!$C:$C, A1:E1,Actual!$I:$I, {"Early";"Late";"Unscheduled";"Unknown"}))

 

[rafuk73]

Thats' brilliant! Thanks

Could you please help with the following formula. I have this formula which calculates totals of time in $F:$F, if an advisor's name is in $B:$B, dates are in $C:$C and statuses are in $G:$G

=SUMPRODUCT(SUMIFS($F:$F,
$B:$B, $B6,
$C:$C, Y4:AC4,
$G:$G, {"ACD";"Meal";"Non-ACD";"Meeting";"Break"}))
"
My questions is how to change statuses in $G:$G so that instead of {"ACD";"Meal";"Non-ACD";"Meeting";"Break"} it was "<>Vacation" , "<>Training" and "<>Unavailable"

In other words it should calculate all statuses in the databases excluding Vacation, Training and Unavailable

Many thanks!

 

[Aladin Akyurek]

Thats' brilliant! Thanks

You are welcome.

Could you please help with the following formula. I have this formula which calculates totals of time in $F:$F, if an advisor's name is in $B:$B, dates are in $C:$C and statuses are in $G:$G

=SUMPRODUCT(SUMIFS($F:$F,
$B:$B, $B6,
$C:$C, Y4:AC4,
$G:$G, {"ACD";"Meal";"Non-ACD";"Meeting";"Break"}))
"
My questions is how to change statuses in $G:$G so that instead of {"ACD";"Meal";"Non-ACD";"Meeting";"Break"} it was "<>Vacation" , "<>Training" and "<>Unavailable"

In other words it should calculate all statuses in the databases excluding Vacation, Training and Unavailable

Many thanks!

Negation calls for a different approach...

=SUMPRODUCT($F$6:$F$600,--($B$6:$B$600=$B6),--ISNUMBER(MATCH($C$6:$C$600,Y4:AC4,0)),1-ISNUMBER(MATCH($G$6:$G$600,{"Vacation","Training","Unavailable"},0)))

Note. It's better to avoid referencing whole columns in this type of formulas for reasons of efficiency.

 

[rafuk73]

Thanks, Aladin! Is there anyway of replacing SUMPRODUCT with SUMIFS as Sumproduct does slow the calculation?
Thanks again