sumifs excluding errors

[adebrux]

Hi all,

I have been reading this forum for a while and always found it to be very useful! I am however now stuck with an issue I can't find an answer for...

I am working on a file where 5 tabs represent 5 producers (each tab as the producer name), and in each tab, I have a standardized budget follow up table. with 2 columns for budget (one: total spend, the other spend per unit), and 2 columns for actuals. This goes on for the 12 months of the year, plus a Full year summary.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]FY[/TD]
[TD]FY[/TD]
[TD]FY[/TD]
[TD]FY[/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Budget[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]actual[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total $[/TD]
[TD]$/unit[/TD]
[TD]Total $[/TD]
[TD]$/Unit[/TD]
[TD]Total $[/TD]
[TD]$/unit[/TD]
[TD]Total $[/TD]
[TD]$/unit[/TD]
[/TR]
[TR]
[TD]Production[/TD]
[TD]1000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cost item 1[/TD]
[TD]100[/TD]
[TD]0.1[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[TD]11[/TD]
[TD]0.11[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[/TR]
[TR]
[TD]cost item 2[/TD]
[TD]50[/TD]
[TD]0.05[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[TD]4.9[/TD]
[TD]0.049[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[/TR]
[TR]
[TD]revenue 1[/TD]
[TD]75[/TD]
[TD]0.075[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[TD]6[/TD]
[TD]0.06[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[/TR]
[TR]
[TD]net cost[/TD]
[TD]75[/TD]
[TD]0.075[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[TD]9.9[/TD]
[TD]0.099[/TD]
[TD][/TD]
[TD]#Div/0![/TD]
[/TR]
</tbody>[/TABLE]

This is a fake example of what it looks like when my budget is in, but not my actuals yet.

Now, I am trying to get a quick comparison tab, where I can just chose a month through a drop down, and then get a snapshot of all my producers next to each others.
I have set up the tab as follow:
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Month[/TD]
[TD]drop down list[/TD]
[TD]Producer 1[/TD]
[TD][/TD]
[TD]producer 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comparo[/TD]
[TD]Budget/actual[/TD]
[TD]Total $[/TD]
[TD]$/unit[/TD]
[TD]total $[/TD]
[TD]$/unit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]production[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]total revenues[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]net costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I easily got the production numbers, since there is a number or nothing in the other tabs
However, when trying to sum up the total costs, the cells I am referencing may include some #DIV/0! errors which do not work with the sumifs formula:

Here is the formula I am using:
=sumifs(indirect("'"&C$1&"'!$B$5:$J$6"),indirect("'&C$1&"'!$B$1:$J$1"),$B$1,indirect("'"&C$1&"'!$B$2:$J$2"),$B$2,indirect("'"&C$1&"'!$B$3:$J$3"),c2)

Where Indirect(C1) gets me to producer 1 tab.
When I run the formula evaluation, I pass all the evaluation steps, but because there are some errors in the "summable" array, it won't return a number....

I have tried to add the following conditions (criteria range, followed by criteria): indirect("'"&C$1&"'!$B$5:$J$6"), isnumber(indirect("'"&C$1&"'!$B$5:$J$6"))

But apparently isnumber always returns false when checking on more that one row....

would you ave an idea on how to run a sumifs that ignore errors????

Thanks & Regards

 

[FDibbins]

Hi, welcome to the Board

However, when trying to sum up the total costs, the cells I am referencing may include some #DIV/0! errors which do not work with the sumifs formula:

So fix the error at source, then the problem should go away?
What is the formula that is causing the error, probably something like =C2/D2 correct?
If so, adjust that to =if(d2="","",c2/d2)

 

[adebrux]

Thanks.

If i didn't have 25 tabs, each with 26 columns and more than 30 line items to change, i would do that.... however my predecessor already filled in half the information for each producers (the budget part) so i can't just bluntly change the tavs without loosing data...
Thus my question about a way to not count the errors cells in a sumifs!

Thanks!

 

[Tetra201]

Here is an error-ignoring analog of your SUMIFS formula with exactly the same ranges/cells (note: to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IF((INDIRECT("'"&C$1&"'!$B$1:$J$1")=$B$1)*(INDIRECT("'"&C$1&"'!$B$2:$J$2")=$B$2)*(INDIRECT("'"&C$1&"'!$B$3:$J$3")=C2),IF(ISNUMBER(INDIRECT("'"&C$1&"'!$B$5:$J$6")),INDIRECT("'"&C$1&"'!$B$5:$J$6"))))

 

[adebrux]

Awesome! thank you, that works perfect!

Thanks for the help!

 

[Tetra201]

You are welcome.