What excel formula do I need for this?
[Closed]
Report
Pichuskin

rizvisa1
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
Related:
 What excel formula do I need for this?
 @ in excel formula ✓  Forum  Excel
 Excel formula if cell contains text then return value in another cell ✓  Forum  Excel
 Excel formula bar missing  HowTo  Excel
 Excel formula to transfer data between worksheets  HowTo  Excel
 Excel formula for position ✓  Forum  Office Software
1 reply
rizvisa1
 Posts
 4476
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 August 2, 2020
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 datestatus 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 datestatus combination multiple times ( value would remain same though)
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?
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?
For other part, I am not sure what you are asking. Let me explain formula to you, and then you should be able to adapt as per your requirement
=IF(AND(A2=A1, C2=C1), "", SUMPRODUCT((A$1:A$15=A2) * ($C1:$C15=C2) *1))
The above formula is saying
IF
Both A1=A1 and C1=C2, then just show me a blank [AND(A2=A1, C2=C1), "" ]
If that is not the case then show me the result of formula
SUMPRODUCT((A$1:A$15=A2) * ($C1:$C15=C2) *1)
Now here is the further breakdown and explanation of formula
First the formula SUMPRODUCT basicially multiply values on various columns on same row and add the result to give you a grand total. Like A1 * B1 + A2 * B2 + A3 * B3 .....
in the formula we are sayinng
Between the cells A1 A15, use only those cells of A that are equal to A2 [ (A$1:A$15=A2) ]
Between the cells C1 C15, use only those cells of C that are equal to C2 [ ($C1:$C15=C2) ]
this would result in an array of TRUE and FALSE ( in both cases)
So at this point you have identified rows that have same date as in cell A2 and Same Status as C2.
We multiply both resulting Arrays of True and False. False is 0 in VB and true = 1
so in essense we are mulitply 0s and 1s. So if any one of the corresponding member of array is false, you end up with a 0. If both are true you would end up with a 1 as 1 * 1 = 1
so now you have an array where you have either a 0 ( if the cell was not equal ot A2 or/and C2) or 1 (if the cell was equal to A2 and C2)
and the last *1 is just to multiply the result by 1 to get our sumproduct.