# How to use excel formulas to calculate this?

What I need to find:
Count:
-ID 1 or ID 2 is from rp
-Particular week (eg.week 80)
-Count only if the status is successful

How the worksheet looks like:

Sheet 2(details of the people and the division they are in):

Sheet 3:

How my code looks like:
=SUMPRODUCT(COUNTIFS('Sheet3’!\$A\$3:\$A\$4747,C1,'Sheet3’!\$E\$3:\$E\$4747,"1",'Sheet3’!\$C\$3:\$C\$4747,Sheet2!B2:B15))

The above code does not work. Any help will be appreciated. Thank you!
Picture6.png
Book2.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultantCommented:
Hi Samsun,

Please can you post a sample file so that we can see what is going on.

Thanks,

Alan.
1
Director of Information TechnologyCommented:
I think you can accomplish this with a Pivot Table.  Just need a really dataset.
Can you supply this?
1
Author Commented:
I have edited the question a bit for better understanding. and have attached the sample dataset. Thank you helping!
0
ConsultantCommented:
Hi Samsun,

What is the answer that you are expecting in Sheet1!C4, compared to D4, E4, F4, G4, and H4?

What determines whether a 'Successful' count goes into each column?

Thanks,

Alan.
1
Author Commented:
Hi Alan,

QN:What is the answer that you are expecting in Sheet1!C4, compared to D4, E4, F4, G4, and H4?:
ANS:In C4 I want to count the ID  of people who are from Rp, then for D4 I want to count those from HP...
QN:What determines whether a 'Successful' count goes into each column?
ANS: The 1 in each column represents a tick. It is a data that was keyed in.
0
Author Commented:
in summary: my question is on sheet1, I'd like there to be a formula in the matrix (e.g. highlighted in yellow), which provides a count (number) of the occurrences of "successful" from sheet3 while in the correct week no. (e.g. 80) and for the corresponding SP and RP references?
0
Business Systems Analyst , ex-Senior Application EngineerCommented:
not really using SUMPRODUCT but come out with my own by using some temporary formula to get the count.

see if attached meet your requirement?
Book2_b.xlsx
0

Experts Exchange Solution brought to you by