Can I use sumproduct for this?

[joec]

is there a way i can use sumproduct or some other formula to data from the
following table for multiple criteria:
Associate Month Quest 1 Quest 2 Quest 3
A jan 10 10 10
B jan 9 9
9
B feb 8 8
8
B mar 9 8
8
C jan 7 7
6
C feb 10 10 10
C feb 10 10
10

specifically what i need to do is count the # of times associate C
has entries for Feb and return the totl # of all questions and the total
of all questions sumed up. The answer for associate C for Feb would be
6 questions for a total of 30. i know this will require 2 different formulas
in 2 separate cells, but that is ok.

 

[RagDyer]

To me, it looks like 6 questions totaling 60, not 30!

If the months are text entries, try this:

=Sumproduct((A2:A8="C")*(B2:B8="feb")*C2:E8)

 

[Pete_UK]

This will give you the count of questions (6):

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<>0)+(D28<>0)+
(E2:E8<>0)))

Not sure why you think the total should be 30 - is it not 60? This one
gives an answer of 60:

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:C8+D28+E2:E8))

Hope this helps.

Pete

 

[joec]

works great thanks!!!
--
joec

This will give you the count of questions (6):

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<>0)+(D28<>0)+
(E2:E8<>0)))

Not sure why you think the total should be 30 - is it not 60? This one
gives an answer of 60:

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:C8+D28+E2:E8))

Hope this helps.

Pete

 

[Teethless mama]

Compact Formulae

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:E8<>0))

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:E8))

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

works great thanks!!!
--
joec





- Show quoted text -