conditional sum across tabs sumif vs sumifs

[DEllis81]

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

 

[T. Valko]

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]

Hi,

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

ZAX

 

[DEllis81]

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]

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?