Dragging down a Indirect formula.

Chocolate_bar

New Member
Joined
May 9, 2015
Messages
10
Hello, being a novel excel user I usually find my answers on this and other forums. This problem has stumped me I have tryed to write the formula many diffrent ways. All do not work as well as this one. I need to be able to have excel update my cell refrences when I drag this formula down. Any help will be appreciated, and I'm sorry if I have written this crudely. =SUM(SUMIF(INDIRECT({"AC12","AE12","AG12","AI12"}),">0")/4)
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Thanks for making me think! Basically I didn't need that Indirect in there, and now the formula can be auto updated!
=(SUMIF(AC12,">0",AC12)+SUMIF(AE12,">0",AE12)+SUMIF(AG12,">0",AG12)+SUMIF(AI12,">0",AI12))/4
Thanks again!!!
 
Upvote 0
Thanks for making me think! Basically I didn't need that Indirect in there, and now the formula can be auto updated!
=(SUMIF(AC12,">0",AC12)+SUMIF(AE12,">0",AE12)+SUMIF(AG12,">0",AG12)+SUMIF(AI12,">0",AI12))/4
Thanks again!!!

Given the data, what is the result that you expect?

Row\Col
AC​
AD​
AE​
AF​
AG​
AH​
AI​
12​
-3​
2​
4​
0​
 
Upvote 0
The result given the data would be 100% complete. Basically i would not have a negative number, but need to sum any % at those addresses. I have 4 things I need to know are done each is 25% of a 100% completion. Hopefully this makes sense lol. I bet there is probably a simple way of doing this that works but this is working in my spread sheet!
Row\Col
AC​
AD​
AE​
AF​
AG​
AH​
AI​
12​
-3​
2​
4​
0​

<tbody>
</tbody>
 
Upvote 0
Given Aladins example, how is it 100%? Why are you checking if >0 if they can't be negative? What is actually in the cells you are checking?
 
Upvote 0
The result given the data would be 100% complete. Basically i would not have a negative number, but need to sum any % at those addresses. I have 4 things I need to know are done each is 25% of a 100% completion. Hopefully this makes sense lol. I bet there is probably a simple way of doing this that works but this is working in my spread sheet!
Row\Col
AC​
AD​
AE​
AF​
AG​
AH​
AI​
12​
-3​
2​
4​
0​

<tbody>
</tbody>

100% how? You are asked to respond on the data as given...

And what is the result when:

Row\Col
AC​
AD​
AE​
AF​
AG​
AH​
AI​
12​
2​
4​
0​

...?
 
Upvote 0
Without me trying to explain my spread sheet the desired result from your table given is 2%, actually both examples are 2%. The formula is based on other formulas in those columns based on percentages if that makes sense. the ">0" is to sum cells that have something in them.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top