Sumproduct Numbers and Text?

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

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(--(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)
 
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
 
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
Click to expand...

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