Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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= |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT question | Excel Worksheet Functions | |||
A SUMPRODUCT question | Excel Discussion (Misc queries) | |||
Sumproduct question | Excel Worksheet Functions | |||
Sumproduct question | Excel Worksheet Functions | |||
Sumproduct Question | Excel Worksheet Functions |