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.
<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:
<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
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