Forum Discussion
Need an assist for Formula
Could i use sum function instead of count for column (F) based on the same criteria?
That's the kind of question I'd encourage you to try to answer yourself in the future. All you'd need to do is change the formula. If it works, it works; if it doesn't, see if there was an error in it. One of the best ways to learn whether or not something works is to try it, and tweak it... You definitely don't need to worry about breaking anything.
In this case, I did go ahead and try it. And it worked. Had to make a change at the end too, in the final IF function. It becomes an IFERROR function because in summing when there were no numbers to sum, I got an error message. The IFERROR function handles that. I've highlighted in bold and underlined type the changes. Note: I also created a new tab, entitled "Summing" so that is also new.
=LET(res,SUM(FILTER('Invoice List'!$F$4:$F$6565,('Invoice List'!$C$4:$C$6565=Summing!C$2)*('Invoice List'!$J$4:$J$6565>=Summing!$A3)*('Invoice List'!$J$4:$J$6565<=Summing!$B3))),IFERROR(res,0))
You'll need the most current version of Excel for this to work (and, I should add, I'm assuming this is actually what you want).
Here's what a count by year and days looks like.
And here's the formula I used.
=LET
(res,
COUNTA(
FILTER('Invoice List'!$A$4:$A$6565,('Invoice List'!$C$4:$C$6565=Aging!C$2)*('Invoice List'!$J$4:$J$6565>=Aging!$A3)*('Invoice List'!$J$4:$J$6565<=Aging!$B3))
),
IF(res=1,0,res)
)
Let us know if that result is what you were seeking. Or if it's totally off base. Or somewhere in between.
- Thank you dear.
Could i use sum function instead of count for column (F) based on the same criteria?Could i use sum function instead of count for column (F) based on the same criteria?
That's the kind of question I'd encourage you to try to answer yourself in the future. All you'd need to do is change the formula. If it works, it works; if it doesn't, see if there was an error in it. One of the best ways to learn whether or not something works is to try it, and tweak it... You definitely don't need to worry about breaking anything.
In this case, I did go ahead and try it. And it worked. Had to make a change at the end too, in the final IF function. It becomes an IFERROR function because in summing when there were no numbers to sum, I got an error message. The IFERROR function handles that. I've highlighted in bold and underlined type the changes. Note: I also created a new tab, entitled "Summing" so that is also new.
=LET(res,SUM(FILTER('Invoice List'!$F$4:$F$6565,('Invoice List'!$C$4:$C$6565=Summing!C$2)*('Invoice List'!$J$4:$J$6565>=Summing!$A3)*('Invoice List'!$J$4:$J$6565<=Summing!$B3))),IFERROR(res,0))
- Thank you very much for letting me know how to solve the problem.
But I have a question, please, can you explain the formula of the equation in a simple way so that I can understand it?
- Thank you dear.
Could i use sum function instead of count for column (F) based on the same criteria
