conditional sum across tabs sumif vs sumifs

DEllis81

Board Regular
Joined
Jan 19, 2009
Messages
106
I am wanting to sum across tabs, with multiple conditions. As I have found on this forum - thu use of a sumif formula works, with limitation of a single condition. However, I have multiple conditions I want to evaluate within hte sum process.


Example 1 - single condition
SUMPRODUCT(SUMIF(INDIRECT(TabGlobal&"!"&$A$1),B9,INDIRECT(TabGlobal&"!"&$A$2)))
I am able to make this formula work...

Example 2, - single condition, with sumifs function
SUMPRODUCT(SUMIFS(INDIRECT(TabGlobal&"!"&$A$2),INDIRECT(TabGlobal&"!"&$A$1),B10))
I converted to the sumifs function, but with single condition, however not able to make work.
I'm assuming - once I get a single condition working with sumifs, I can add second criteria?

And for clarification - B9, and B10 have same value in my testing...

Please advise, on why the second formula (w/sumifs) is not working. and whether or not, by adding a second criteria - if a valid calculation could be expected.

thanks

thanks
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
I am wanting to sum across tabs, with multiple conditions. As I have found on this forum - thu use of a sumif formula works, with limitation of a single condition. However, I have multiple conditions I want to evaluate within hte sum process.


Example 1 - single condition
SUMPRODUCT(SUMIF(INDIRECT(TabGlobal&"!"&$A$1),B9,INDIRECT(TabGlobal&"!"&$A$2)))
I am able to make this formula work...

Example 2, - single condition, with sumifs function
SUMPRODUCT(SUMIFS(INDIRECT(TabGlobal&"!"&$A$2),INDIRECT(TabGlobal&"!"&$A$1),B10))
I converted to the sumifs function, but with single condition, however not able to make work.
I'm assuming - once I get a single condition working with sumifs, I can add second criteria?

And for clarification - B9, and B10 have same value in my testing...

Please advise, on why the second formula (w/sumifs) is not working. and whether or not, by adding a second criteria - if a valid calculation could be expected.

thanks

thanks
Why don't you tell us what the multiple criteria are.
 

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
Hi,

Well, I tried both of the functions and they work perfectly, The function returns 0 if criteria isn't met.

ZAX
 

DEllis81

Board Regular
Joined
Jan 19, 2009
Messages
106
Ok, thanks... After posting my initial question, I recalled another spreadsheet model, where teh sumifs() formula does work. I'm confused why this particular sumif examples returns a #value error... So, got to be something I am doing wrong.

The second criteria I was hoping to incorporate was the summation across columns (monthly intervals), with criteria being date intervals.
 

DEllis81

Board Regular
Joined
Jan 19, 2009
Messages
106
Ok, a little more research...

What I am finding, if the range name bdata (A2) in above formula - if the range name is a single column, then the sumifs()function works. However, if I expand the bdata range name to two columns, the sumifs() function does not work.

In both cases, of my changing the bdata range columns, the sumif() function worked.

Is this repeatable or easy workaround?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,560
Messages
5,596,842
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top