What excel formula do I need for this?

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am trying to summarize the number of projects being processed everyday and i want to determine how many projects are approved, hold and returned per day. Say in column A1 is the date project submitted, B1 is the project name, C1 is the status of the project, D1 is the date the project was approved/hold/returned and E1 is the approver's name.

Example: Day 1:

Date Project Submitted Project Name Status of Project Date Processed Approver
Jul 1 Proj_001 Approved Jul 1 Ram
Jul 1 Proj_002 Approved Jul 1 Ram
Jul 1 Proj_003 Hold Jul 1 Ram
Jul 1 Proj_004 Approved Jul 1 Jrm
Jul 1 Proj_005 Approved Jul 1 Jrm
Jul 1 Proj_006 Hold Jul 1 Ram
Jul 1 Proj_007 Returned Jul 1 Ram
Jul 1 Proj_008 Approved Jul 1 Jrm
Jul 1 Proj_009 Approved Jul 1 Ram
Jul 2 Proj_010 Approved Jul 2 Ram
Jul 2 Proj_011 Approved Jul 2 Jrm
Jul 2 Proj_012 Hold Jul 2 Jrm
Jul 3 Proj_013 Approved Jul 3 Ram
Jul 3 Proj_014 Returned Jul 3 Ram

What formula should i used for this? I already tried COUNTIF but could not make it work. Pleas help.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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)
Thanks for explaining this to me... I tried it again and it did worked. so glad i asked.

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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
The formula was based on date in column A. It seems that you want the formula to be based on column D. So you can make that change in the formula by replacing "A" with "D"
thanks... i checked it already. looks good. though i'm getting a wrong data for one cell.

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?
also, how do i keep track of the number of hold project per day even though i approved them the following day or week or month? coz as i said the number of hold projects change as i approve them.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
For your first question about other file, you wont know till you try it. Just make sure that ranges refer not only to column but to the book/sheet in case of other book. Most easy way would have the formula as it is, then open the other book and then click on the fomula part where other book needs to be refered. You would see how you need to change the 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.