How to use excel formulas to calculate this?

What I need to find:
-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:
How my code looks like:

The above code does not work. Any help will be appreciated. Thank you!
samsun razviyahAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Hi Samsun,

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


yo_beeDirector of Information TechnologyCommented:
I think you can accomplish this with a Pivot Table.  Just need a really dataset.
Can you supply this?
samsun razviyahAuthor Commented:
I have edited the question a bit for better understanding. and have attached the sample dataset. Thank you helping!
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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?


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.
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?
Ryan ChongBusiness 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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
samsun razviyahAuthor Commented:
Thank u!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.