Forum Discussion
Need an assist for Formula
Dears,
Kindly need your assist regarding the attached sheet as i need to classify the date to be like the below
| Parameter | 2021 | 2020 | 2019 |
| 0-30 | |||
| 31-60 | |||
| 61-90 | |||
| 91-120 | |||
| 121-150 | |||
| 151-180 | |||
| 181-210 | |||
| 211-240 | |||
| 241-270 | |||
| 271-300 | |||
| 301-330 | |||
| 331-360 | |||
| 360-390 (point of loss) |
Year (C) & dayes of collection (J)
Thanks in advance
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))
10 Replies
- Could you please explain how did you do the pivot table you've mentioned in a simple steps like you did and thanks in advance 🙏
Explaining how to create a pivot table in two rows is beyond my knowledge.
I'm not a pivot expert, but here's a little guide that I used and started making pivot tables.
First of all, if you look at the inserted file, I have transferred your specifications to a table in the "invoice list" worksheet. The pivot table was then created from this table.
Hope I was able to help you with this information.
I know I don't know anything (Socrates)
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 dear.
Could i use sum function instead of count for column (F) based on the same criteria
