What excel formula do I need for this?

Closed
Pichuskin - Jul 19, 2010 at 05:27 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Jul 21, 2010 at 07:32 AM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
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)
0
Thanks rizvisa1 for your quick respond. I did that already to get the total number of projects evaluated per day. What I was actually looking for is the breakdown of projects. Like how many is approved on jul 1 / how many is hold on jul 1 / and how many is returned on jul 1. Sorry if I confused you with my question. But I really appreciated your help on this.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jul 19, 2010 at 08:36 AM
The formula I suggested up, does NOT give you count for a day. It gives you count per day per status as you want
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
0
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?
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jul 21, 2010 at 06:17 AM
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"
0
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?
0