Can I use sumproduct for this?
 

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.





--
joec

Can I use sumproduct for this?
 

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)


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"joec" wrote in message
...
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.





--
joec

Can I use sumproduct for this?
 

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

On Feb 21, 11:09*pm, joec wrote:
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.

--
joec

Can I use sumproduct for this?
 

works great thanks!!!
--
joec


"Pete_UK" wrote:

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

On Feb 21, 11:09 pm, joec wrote:
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.

--
joec

Can I use sumproduct for this?
 

Compact Formulae

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

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


"Pete_UK" wrote:

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

On Feb 21, 11:09 pm, joec wrote:
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.

--
joec

Can I use sumproduct for this?
 

You're welcome - thanks for feeding back.

Pete

On Feb 22, 1:26*am, joec wrote:
works great thanks!!!
--
joec



"Pete_UK" wrote:
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

On Feb 21, 11:09 pm, joec wrote:
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.

--
joec- Hide quoted text -

- Show quoted text -