Sumproduct question

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

wx4usa

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.
 
wx4usa said:
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.
Click to expand...

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,COUNTIF(D:D,"Bob"))))

You could put the dates together with something like this:

=DATE($C15,LOOKUP($A15,{"Jan","Feb","Mar","Apr","May"},{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=
 
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
 
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,COUNTIF(D:D,"Bob"))))

You could put the dates together with something like this:

=DATE($C15,LOOKUP($A15,{"Jan","Feb","Mar","Apr","May"},{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=
Click to expand...

Month is a word.
 
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
Click to expand...

Thanks very much Barb, That did the trick!
 
Month is a word.
Click to expand...

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=
 
Back
Top