Excel Fomula Help - SUMPRODUCT Hi All, I am an apprentice at work and am trying

Question

Excel Fomula Help - SUMPRODUCT

Hi All,

I am an apprentice at work and am trying hard to show off my excel skills..

However.

I am well and truly stumped with the following forumla:

=SUMPRODUCT((!A:A="IT Department")*(B:B="A"))*(C:C="1")

I am trying to count 3 things - Firstly if they sit in the department, and secondly if they have scored an 'A'.. On top of this I also want to count whether they have scored a '1'..

This is so i can count how many people in IT scored an 'A1' rating.

It will work in two parts, however when i try to add the 1 part to my count, it does not work.

Any help would be greatly appreciated!!!

I also have another similar problem:

I am trying to count how many forms have been returned VS department, however this is marked by a date, e.g. 'Date form Recieved'. So if the cell was populated, that would be one return for IT..

Does anyone know a way i can count how many forms have been returned VS department?

=SUMPRODUCT((A:A="IT Department")*???

Thank you all for your help in advance...

Explanation / Answer

Hi,

You were very close, try it this way..

=SUMPRODUCT((A:A="IT Department")*(B:B="A")*(C:C=1)).

A couple of points. Using full will slow things down so use a smaller range (say) 10k rows

=SUMPRODUCT((A1:A10000="IT Department")*(B1:B10000="A")*(C1:C10000=1)).

Also note at the end I removed the quotes from around the 1,, having the quotes makes it text and a text 1 is not the same as a numeric 1

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.