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:
 
sheeSheet 2(details of the people and the division they are in):
sheet 1
Sheet 3:
engagement
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
samsun razviyahAsked:
Who is Participating?
 
Ryan ChongCommented:
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
 
AlanConsultantCommented:
Hi Samsun,

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

Thanks,

Alan.
1
 
yo_beeDirector of Information TechnologyCommented:
I think you can accomplish this with a Pivot Table.  Just need a really dataset.
Can you supply this?
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
samsun razviyahAuthor Commented:
I have edited the question a bit for better understanding. and have attached the sample dataset. Thank you helping!
0
 
AlanConsultantCommented:
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
 
samsun razviyahAuthor 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
 
samsun razviyahAuthor 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
 
samsun razviyahAuthor 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.

All Courses

From novice to tech pro — start learning today.