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.

	FY	FY	FY	FY	JAN	JAN	JAN	JAN
	Budget	Budget	Actual	Actual	Budget	Budget	Actual	actual
	Total $	$/unit	Total $	$/Unit	Total $	$/unit	Total $	$/unit
Production	1000				100
cost item 1	100	0.1		#Div/0!	11	0.11		#Div/0!
cost item 2	50	0.05		#Div/0!	4.9	0.049		#Div/0!
revenue 1	75	0.075		#Div/0!	6	0.06		#Div/0!
net cost	75	0.075		#Div/0!	9.9	0.099		#Div/0!

<tbody>
</tbody>

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:

Month	drop down list	Producer 1		producer 2
Comparo	Budget/actual	Total $	$/unit	total $	$/unit
	production
	total costs
	total revenues
	net costs

<tbody>
</tbody>

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.