sumifs excluding errors

adebrux

New Member
Joined
Dec 2, 2016
Messages
3
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.

FYFYFYFYJANJANJANJAN
BudgetBudgetActualActualBudgetBudgetActualactual
Total $$/unitTotal $$/UnitTotal $$/unitTotal $$/unit
Production1000100
cost item 11000.1#Div/0!110.11#Div/0!
cost item 2500.05#Div/0!4.90.049#Div/0!
revenue 1750.075#Div/0!60.06#Div/0!
net cost750.075#Div/0!9.90.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:
Monthdrop down listProducer 1producer 2
ComparoBudget/actualTotal $$/unittotal $$/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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Dec 2, 2016
Messages
3
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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):

Code:
=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"))))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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