What excel formula do I need for this?
Closed
Pichuskin
-
Jul 19, 2010 at 05:27 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 21, 2010 at 07:32 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 21, 2010 at 07:32 AM
Related:
- What excel formula do I need for this?
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Kernel for excel - Download - Backup and recovery
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jul 19, 2010 at 06:10 AM
Jul 19, 2010 at 06:10 AM
If you add this line on F2 and drag it down to row 15 (based on you sample)
=IF(AND(A2=A1, C2=C1), "", SUMPRODUCT((A$1:A$15=A2) * ($C1:$C15=C2) *1))
This is because you did not mention where the date and status are. This is will add show the count, if the date or status of a row is different from the one above it. Of course this means if dates and status are mixed, you would get sum for same date-status combination multiple times ( value would remain same though)
=IF(AND(A2=A1, C2=C1), "", SUMPRODUCT((A$1:A$15=A2) * ($C1:$C15=C2) *1))
This is because you did not mention where the date and status are. This is will add show the count, if the date or status of a row is different from the one above it. Of course this means if dates and status are mixed, you would get sum for same date-status combination multiple times ( value would remain same though)
Jul 19, 2010 at 06:43 AM
Jul 19, 2010 at 08:36 AM
SUMPRODUCT((A$1:A$15=A2) * ($C1:$C15=C2)
col A: Date
Col C : Status
As I indirectly mentioned that it is based on assumption that data is sorted on date - Status, else you can have count repeated
Jul 1 Hold
Jul 1 Approved
Jul 1 Hold
If data is like that it will give you two times the count of July 1 - Hold (one for each occurance) as
Jul 1 Hold 2
Jul 1 Approved 1
Jul 1 Hold 2
What you should do is on new sheet have all the dates and all the status
Jul1 Hold
Jul1 Approved
Jul1 Retured
and then you can use the above formula as (lets say your data is on sheet1)
=IF(SUMPRODUCT((Sheet1!A$1:A$15=A2) * (Sheet1!$C1:$C15=C2) *1)
DISCLAIMER: I have not tested it on two sheet design
Jul 21, 2010 at 06:06 AM
However, I noticed that with this formula, it also count the project i was not able to review on that day but was able to review and approve it the following day. The count falls on the date it was submitted and not on the actual date it was approved.
my other problem is when i change the status from hold to approved.
example: for july 1: 6 projects were reviewed = 4 are approved and 2 are hold on that day. then comes july5 i approved 1 of the projects i put on hold from july 1.
Now, I noticed that changing the status from hold to approved the count changes also, it now becomes approved = 5 & hold = 1. Technically this should add to July 5 approved. But that's not how I want it.
what I was thinking, which is my failure to mention earlier, is to be able to monitor also the number of projects i put on hold for the month but later approved (on a per day basis also), either within the month or the following month.
So the report should show:
Approved
Hold
Returned
Approved on Hold from current month
Approved on Hold from previous month
is this possible?
Jul 21, 2010 at 06:17 AM
Jul 21, 2010 at 06:41 AM
anyway, what about if the report is on a separate file? say the data is in file 1 and the report is in file 2? will it be the same formula?