Sumproduct question
 

I just want to count the number of entries, not a sum of totals.
column A is Month, B is Day, C is year, D is Name

Can I use sumproduct to just return the number of entries for "Bob" on
August, 1, 2007? Bob may have anywhere from 1-20 lines of data in
this scenario, I just want to know how many occurrences, not an actual
sum of some numbers.

Sumproduct question
 

In . com, wx4usa
spake thusly:

I just want to count the number of entries, not a sum of totals.
column A is Month, B is Day, C is year, D is Name

Can I use sumproduct to just return the number of entries for
"Bob" on August, 1, 2007? Bob may have anywhere from 1-20
lines of data in this scenario, I just want to know how many
occurrences, not an actual sum of some numbers.

You're not giving sufficient information. Is the month a word
("April"), and abbreviation ("Apr" or "Apr."), or a number?

Assuming a word, well, this works, but it's also an array
formula.

=IF(A2:A29="Apr",IF(B2:B29=2,IF(C2:C29=1999,COUNTI F(D:D,"Bob"))))

You could put the dates together with something like this:

=DATE($C15,LOOKUP($A15,{"Jan","Feb","Mar","Apr","M ay"},{1,2,3,4,5}),$B15)


(You'd want to go through December; I just shortened it here
to make it easier.)

There may be other ways.

=dman=

Sumproduct question
 

Without knowing specifically what you have in column A, B or C, here is my
suggestion

=SUMPRODUCT(--(A1:A100="August"),--(B1:B100=1),--(C1:C100=2007),
--(D1:D100="Bob"))

HTH,
Barb Reinhardt
"wx4usa" wrote:

I just want to count the number of entries, not a sum of totals.
column A is Month, B is Day, C is year, D is Name

Can I use sumproduct to just return the number of entries for "Bob" on
August, 1, 2007? Bob may have anywhere from 1-20 lines of data in
this scenario, I just want to know how many occurrences, not an actual
sum of some numbers.

Sumproduct question
 

On Aug 9, 6:55 pm, Dallman Ross <dman@localhost. wrote:
In . com, wx4usa
spake thusly:

I just want to count the number of entries, not a sum of totals.
column A is Month, B is Day, C is year, D is Name

Can I use sumproduct to just return the number of entries for
"Bob" on August, 1, 2007? Bob may have anywhere from 1-20
lines of data in this scenario, I just want to know how many
occurrences, not an actual sum of some numbers.

You're not giving sufficient information. Is the month a word
("April"), and abbreviation ("Apr" or "Apr."), or a number?

Assuming a word, well, this works, but it's also an array
formula.

=IF(A2:A29="Apr",IF(B2:B29=2,IF(C2:C29=1999,COUNTI F(D:D,"Bob"))))

You could put the dates together with something like this:

=DATE($C15,LOOKUP($A15,{"Jan","Feb","Mar","Apr","M ay"},{1,2,3,4,5}),$B15)

(You'd want to go through December; I just shortened it here
to make it easier.)

There may be other ways.

=dman=

Month is a word.

Sumproduct question
 

On Aug 9, 6:58 pm, Barb Reinhardt
wrote:
Without knowing specifically what you have in column A, B or C, here is my
suggestion

=SUMPRODUCT(--(A1:A100="August"),--(B1:B100=1),--(C1:C100=2007),
--(D1:D100="Bob"))

HTH,
Barb Reinhardt

"wx4usa" wrote:
I just want to count the number of entries, not a sum of totals.
column A is Month, B is Day, C is year, D is Name

Can I use sumproduct to just return the number of entries for "Bob" on
August, 1, 2007? Bob may have anywhere from 1-20 lines of data in
this scenario, I just want to know how many occurrences, not an actual
sum of some numbers.

Thanks very much Barb, That did the trick!

Sumproduct question
 

In . com, wx4usa
spake thusly:

I just want to count the number of entries, not a sum of
totals. column A is Month, B is Day, C is year, D is Name

Month is a word.

I don't understand your objection to SUMPRODUCT. It seems
to me to be the easiest approach here.

Suppose something like this:

Month Day Year Salesperson
Jan 1 2005 Phil
Feb 2 2006 Mary
Mar 3 2007 Phil
Apr 4 2008 Debra
Jan 1 2005 Pete
Jan 1 2004 Phil
Jan 1 2005 Phil
Feb 2 2006 Mary
Mar 3 2007 Phil
Apr 4 2008 Debra
Jan 1 2005 Pete
Jan 1 2004 Phil
Jan 1 2005 Phil
Feb 2 2006 Mary
Mar 3 2007 Phil
Apr 4 2008 Debra
Jan 1 2005 Pete
Jan 1 2004 Phil
Jan 1 2005 Phil
Feb 2 2006 Mary
Mar 3 2007 Phil
Apr 4 2008 Debra
Jan 1 2005 Pete
Jan 1 2004 Phil
Jan 1 2005 Phil
Feb 2 2006 Mary
Mar 3 2007 Phil


If you just use COUNTIF, you'll need a series of IF-statements
or, at best, a messy compound single IF-statement to count
the number of Phils on 1/1/2005. Or you could use SUMPRODUCT
as already shown.

=SUMPRODUCT(--($A$2:$A$28="Jan"),--($B$2:$B$28=1),--($C$2:$C$28=2005),--(D$2:$D$28="Phil"))

Okay, I just double-checked, and it doesn't seem to need to be an
array formula. Well, that's good, I guess.

You could also do it like this, allowing you to drag the formula
down and see the count for each line. Of course, you'll be
repeating information whenever the data repeats:

=SUMPRODUCT(--($A$2:$A$28=$A2),--($B$2:$B$28=$B2),--($C$2:$C$28=$C2),--(D$2:$D$28=$D2))

Another approach is to use a helper-column to combine
the dates. Then you could use COUNTIF easily. E.g., helper-column E2
could contain:

=VALUE(A2 & " " & B2 & ", " & C2)

Drag that down. You can format it as a date now if you want
and have it recognized as a date. At least it's an improvement
over three columns for the date. You could use SUMPRODUCT on
that, too, or use one IF-statement. Or put all the columns
togther as a text value in your helper-column, and use COUNTIF.

Others' comments would be apreciated to flesh this out further as
needed.

=dman=