Can I use sumproduct for this?

J

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.
 
R

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)
 
P

Pete_UK

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

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<>0)+(D2:D8<>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+D2:D8+E2:E8))

Hope this helps.

Pete
 
J

joec

works great thanks!!!
--
joec


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

=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<>0)+(D2:D8<>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+D2:D8+E2:E8))

Hope this helps.

Pete
 
T

Teethless mama

Compact Formulae

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top