sumproduct or sumif formula help
 

I wonder if someone could tell me which formula to use to do the following:

A1= the sum of the yes responses from each sales rep in a quarter (Jan-Mar)

Month SalesRep Response
Jan John No
Feb John Yes
Mar Bill Yes
Apr John No

Answer = 1 yes response for JOHN in the first quarter (jan-Mar)

Answer = 1 yes response for Bill in the first quarter

I am using name ranges .i.e Month SalesRep Response

Thank you in advance for any help you can provide
Ferde

sumproduct or sumif formula help
 

=SUMPRODUCT((ISNUMBER(MATCH(B1:B10,{"Jan","Feb","M ar"},0)))*(C1:C10="John")*(D1:D10="Yes"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ferde" wrote in message
...
I wonder if someone could tell me which formula to use to do the following:

A1= the sum of the yes responses from each sales rep in a quarter
(Jan-Mar)

Month SalesRep Response
Jan John No
Feb John Yes
Mar Bill Yes
Apr John No

Answer = 1 yes response for JOHN in the first quarter (jan-Mar)

Answer = 1 yes response for Bill in the first quarter

I am using name ranges .i.e Month SalesRep Response

Thank you in advance for any help you can provide
Ferde

sumproduct or sumif formula help
 

=SUMPRODUCT((B2:B11={"Jan","Feb","Mar"})*(C2:C11=" John")*(D2:D11="Yes"))

"ferde" wrote:

I wonder if someone could tell me which formula to use to do the following:

A1= the sum of the yes responses from each sales rep in a quarter (Jan-Mar)

Month SalesRep Response
Jan John No
Feb John Yes
Mar Bill Yes
Apr John No

Answer = 1 yes response for JOHN in the first quarter (jan-Mar)

Answer = 1 yes response for Bill in the first quarter

I am using name ranges .i.e Month SalesRep Response

Thank you in advance for any help you can provide
Ferde

sumproduct or sumif formula help
 

THANK YOU IT WORKS GREAT

"Bob Phillips" wrote:

=SUMPRODUCT((ISNUMBER(MATCH(B1:B10,{"Jan","Feb","M ar"},0)))*(C1:C10="John")*(D1:D10="Yes"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ferde" wrote in message
...
I wonder if someone could tell me which formula to use to do the following:

A1= the sum of the yes responses from each sales rep in a quarter
(Jan-Mar)

Month SalesRep Response
Jan John No
Feb John Yes
Mar Bill Yes
Apr John No

Answer = 1 yes response for JOHN in the first quarter (jan-Mar)

Answer = 1 yes response for Bill in the first quarter

I am using name ranges .i.e Month SalesRep Response

Thank you in advance for any help you can provide
Ferde

sumproduct or sumif formula help
 

THANK YOU THIS WORKS GREAT TOO.

"BoniM" wrote:

=SUMPRODUCT((B2:B11={"Jan","Feb","Mar"})*(C2:C11=" John")*(D2:D11="Yes"))

"ferde" wrote:

I wonder if someone could tell me which formula to use to do the following:

A1= the sum of the yes responses from each sales rep in a quarter (Jan-Mar)

Month SalesRep Response
Jan John No
Feb John Yes
Mar Bill Yes
Apr John No

Answer = 1 yes response for JOHN in the first quarter (jan-Mar)

Answer = 1 yes response for Bill in the first quarter

I am using name ranges .i.e Month SalesRep Response

Thank you in advance for any help you can provide
Ferde