I want to get the count of cells used in an excel function.
For example say I have a sum function ='CV'!D11+Farmer!D11+'County'!D11+Rt!D11+WT!D11+'Country'!D11
I need a function that will tell me how many cells were used to get the total sum. In this case it is 6. The tricky part is if one of the cells used is blank I do not want it counted. For instance say cell D11 on the Farmer sheet is blank I do not want it counted in the total. So the total should be 5.
Add a comment
|
3 Answers
Use COUNT:
=COUNT('CV'!D11,Farmer!D11,'County'!D11,Rt!D11,WT!D11,'Country'!D11)
It will only count the cell if it has a number
2 Comments
KiraLightYagami
Yes. However I need the count of the cells used in the sum function. so if I am summing 6 cells and 1 is blank I need a count of 5 to be displayed.
Scott Craner
I am not understanding then. @KiraLightYagami because that is what this will do. Using the same six cells from the sum, it would return
5 if one of them were blank.You should really try to collate all your data in to a single sheet before running calculations. For the sake of example, I'll assume you have it in the range A1:A5, then you can add handling of the various cases using array formulas:
- Get the count of non-empty cells (the
ISBLANKfunction is untrustworthy in my experience): {SUM(IF(LEN(A1:A5)>0,1,0))} - Get the sum of those cells:
SUM(A1:A5)
(must use Ctrl+Shift+Enter to enter the formula as an array formula, you will know it worked if the formula shows like {IF(...)} with the curly brackets)
Because blank/missing values are treated implicitly as 0 in the SUM function, this case is simple. If you have other validations then you'd have to write an array formula for the summation as well. For example, only including numbers between a min and max threshold (e.g. if you want to exclude outliers):
{SUM(IF(AND(A1:A5 >= yourMinValue, A1:A5 < yourMaxValue), A1:A5, 0)}.
6 Comments
Scott Craner
Why the array formulas with SUMIFS and COUNTIFS would do the same as your formula.
Elaskanator
Is that a question? And yes, there are numerous ways to skin a cat.
Scott Craner
I am curious, why. Why would you suggest the sluggish high overhead and longer array formula over the shorter optimized sumifs and countifs? I am truly curious. I want to know if you know something about why this is more efficient. I am always trying to learn.
Scott Craner
Agreed that there are times that the only method is an array formula, and I live by them. But when a simpler more efficient method exists/works I personally prefer them as they are easier to maintain. Thanks for you explanation. And I personally hate helper columns also. But...isn't that what you are technically asking the OP to do here, by creating a copy of the data on a master sheet?
Elaskanator
Copying the data just vastly helps to maintain the calculation formula if the business logic ever changes, because copying
{'CV'!D11+Farmer!D11+'County'!D11+Rt!D11+WT!D11+'Country'!D11} to every single formula and reference will cause you to descend in to insanity ;) I believe it is often appropriate to sacrifice some brevity for the sake of extensibility/maintainability (i.e. using array formulas), which also includes the overhead of compartmentalizing (copying the distributed source data to a continuous cell range). |
If I understand your question correctly, you want to literately count the number of cells used in a formula which in your example is summing 6 values from 6 different locations.
I used the following example to demonstrate my solution:
The sum of =A1+B1+C1+D1+E1+F1 is 10 where cell C1 has a 0 value in it but cell E1 is blank.
Using the following array formula I was able to count the number of cells that have a value other than 0:
=SUMPRODUCT(IFERROR(ABS(N(INDIRECT(TRIM(MID(SUBSTITUTE(RIGHT(FORMULATEXT(A3),LEN(FORMULATEXT(A3))-1),"+",REPT(" ",100)),100*ROW(INDIRECT("1:"&LEN(FORMULATEXT(A3))))-99,100)))))>0,0)*1)
Please note you MUST press Ctrl+Shift+Enter upon finishing the formula in the formula bar otherwise they will not function correctly.
The logic is to use a combination of TRIM+MID+SUBSTITUTE+RIGHT+FORMULATEXT+REPT+ROW+INDIRECT to extract the cell addresses from the original formula, then use INDIRECT to convert the cell address into the values stored in those cells, then use a combination of IFERROR+ABS+N to find out if any of these values are not
0, and lastly use SUMPRODUCT to add up all theTRUEresults.
It is obvious that there are a couple limitations of my solution:
- If your actual formula is not strictly in the form of
A+B+C+D+E+F, then my SUBSTITUTE part of formula will need further modification; - The formula will treat cells containing
0as blank and does not include them in the count.
Let me know if you have any questions. Cheers :)
