| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in SUMPRODUCT formula...
Currently I'm encounting error for SUMPRODUCT formula:
Name Judgement Date John 1 Jan-08 Sarah 1 Jan-08 John 1 Feb-08 John 0 Jan-08 John 1 Jan-08 What formula can I use so that the count will be 2 for name "John", judgement "1", month "Jan" & year "2008"? Please help.... Thank you. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in SUMPRODUCT formula...
One way:
=SUMPRODUCT(--(A2:A100="John"),--(B2:B100=1),--(MONTH(C2:C100)=1), --(YEAR(C2:C100)=2008)) In article , Astro wrote: Currently I'm encounting error for SUMPRODUCT formula: Name Judgement Date John 1 Jan-08 Sarah 1 Jan-08 John 1 Feb-08 John 0 Jan-08 John 1 Jan-08 What formula can I use so that the count will be 2 for name "John", judgement "1", month "Jan" & year "2008"? Please help.... Thank you. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in SUMPRODUCT formula...
It depends on whether you have dates or text in the third column.
If text: =sumproduct(--(a2:a1000="John"),--(b2:b1000=1),--(c2:c1000="Jan-08")) If dates: =sumproduct(--(a2:a1000="John"),--(b2:b1000=1),--(text(c2:c1000,"mmm-yy")="Jan-08")) Regards, Fred "Astro" wrote in message ... Currently I'm encounting error for SUMPRODUCT formula: Name Judgement Date John 1 Jan-08 Sarah 1 Jan-08 John 1 Feb-08 John 0 Jan-08 John 1 Jan-08 What formula can I use so that the count will be 2 for name "John", judgement "1", month "Jan" & year "2008"? Please help.... Thank you. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help in SUMPRODUCT formula...
Try using the =Dcount formula instead.
Name Judgement Date John 1 Jan-08 Name Judgement Date John 1 Jan-08 Sarah 2 Jan-08 John 1 Feb-08 John 0 Jan-08 John 1 Jan-08 =DCOUNT(A4:C9,"Judgement",A1:C2) cell A1 is the first name heading, while cell A4 is the second name heading. "Astro" wrote: Currently I'm encounting error for SUMPRODUCT formula: Name Judgement Date John 1 Jan-08 Sarah 1 Jan-08 John 1 Feb-08 John 0 Jan-08 John 1 Jan-08 What formula can I use so that the count will be 2 for name "John", judgement "1", month "Jan" & year "2008"? Please help.... Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with SUMPRODUCT formula | Excel Discussion (Misc queries) | |||
| Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
| formula for sumproduct | Excel Discussion (Misc queries) | |||
| Help - Looking for a Sumproduct formula | Excel Worksheet Functions | |||
| sumproduct formula | Excel Worksheet Functions | |||
Linear Mode
