# 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?

Commented:
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

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

Author Commented:
Thank u!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.