Sumproduct Numbers and Text?
 

I have columns set up. Some have names and some have numbers.

Can I use sumproduct to do the following?

Column A is State Text
Column B is Date Month
Column C is Mode Number
Column D is Name Text

Can I use sumproduct to select state, date, mode, and then count the
occurances of a specific name in column D?

Count, not sum? Is that possible?

Sumproduct Numbers and Text?
 

=SUMPRODUCT(--(A2:A200="Texas"),--(B2:B200=--"2006-01-12"),--(C2:C200=27),--(D2:D200="Bob"))

--
---
HTH

Bob

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



"wx4usa" wrote in message
ups.com...
I have columns set up. Some have names and some have numbers.

Can I use sumproduct to do the following?

Column A is State Text
Column B is Date Month
Column C is Mode Number
Column D is Name Text

Can I use sumproduct to select state, date, mode, and then count the
occurances of a specific name in column D?

Count, not sum? Is that possible?

Sumproduct Numbers and Text?
 

Yes, you can do that. Are there any empty cells in your date range? An empty
cell will evaluate as month 1.

=SUMPRODUCT(--(A1:A10="Pa"),--(MONTH(B1:B10)=7),--(C1:C10=5),--(D1:D10="Lisa"))

Better to use cells to hold the criteria:

F1 = Pa
G1 = month number = 7
H1 = 5
I1 = Lisa

=SUMPRODUCT(--(A1:A10=F1),--(MONTH(B1:B10)=G1),--(C1:C10=H1),--(D1:D10=I1))

Biff

"wx4usa" wrote in message
ups.com...
I have columns set up. Some have names and some have numbers.

Can I use sumproduct to do the following?

Column A is State Text
Column B is Date Month
Column C is Mode Number
Column D is Name Text

Can I use sumproduct to select state, date, mode, and then count the
occurances of a specific name in column D?

Count, not sum? Is that possible?

Sumproduct Numbers and Text?
 

On Feb 13, 5:09 pm, "T. Valko" wrote:
Yes, you can do that. Are there any empty cells in your date range? An empty
cell will evaluate as month 1.

=SUMPRODUCT(--(A1:A10="Pa"),--(MONTH(B1:B10)=7),--(C1:C10=5),--(D1:D10="Lisa"))

Better to use cells to hold the criteria:

F1 = Pa
G1 = month number = 7
H1 = 5
I1 = Lisa

=SUMPRODUCT(--(A1:A10=F1),--(MONTH(B1:B10)=G1),--(C1:C10=H1),--(D1:D10=I1))

Biff

"wx4usa" wrote in message

ups.com...

I have columns set up. Some have names and some have numbers.

Can I use sumproduct to do the following?

Column A is State Text
Column B is Date Month
Column C is Mode Number
Column D is Name Text

Can I use sumproduct to select state, date, mode, and then count the
occurances of a specific name in column D?

Count, not sum? Is that possible?

Thank you both very much...sorry for the delay in getting back It
works!

Sumproduct Numbers and Text?
 

You're welcome!

Biff

"wx4usa" wrote in message
ps.com...
On Feb 13, 5:09 pm, "T. Valko" wrote:
Yes, you can do that. Are there any empty cells in your date range? An
empty
cell will evaluate as month 1.

=SUMPRODUCT(--(A1:A10="Pa"),--(MONTH(B1:B10)=7),--(C1:C10=5),--(D1:D10="Lisa"))

Better to use cells to hold the criteria:

F1 = Pa
G1 = month number = 7
H1 = 5
I1 = Lisa

=SUMPRODUCT(--(A1:A10=F1),--(MONTH(B1:B10)=G1),--(C1:C10=H1),--(D1:D10=I1))

Biff

"wx4usa" wrote in message

ups.com...

I have columns set up. Some have names and some have numbers.

Can I use sumproduct to do the following?

Column A is State Text
Column B is Date Month
Column C is Mode Number
Column D is Name Text

Can I use sumproduct to select state, date, mode, and then count the
occurances of a specific name in column D?

Count, not sum? Is that possible?

Thank you both very much...sorry for the delay in getting back It
works!