Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
I've read through loads of the examples but cant seem to get them to work.
Column E has text (a job) column I has a name (JW). there are 31 sheets (one per day) in the workbook. On sheet 32 (just added) i want to total how many jobs JW has done in the month. Tried using =SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW")) but it keeps changing to =SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4)) how do I get around this? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
Chris,
You need to use THREED('1:31'!E1:E60) in your SUMPRODUCT formula. THREED isn't standard and you need to get it as an add-in. I believe it's Morefunc available at http://xcell05.free.fr/ v/r -- Brian "chrisk" wrote: I've read through loads of the examples but cant seem to get them to work. Column E has text (a job) column I has a name (JW). there are 31 sheets (one per day) in the workbook. On sheet 32 (just added) i want to total how many jobs JW has done in the month. Tried using =SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW")) but it keeps changing to =SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4)) how do I get around this? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
Crashed with 'Fatal Error' when I tried to download it.
"Brian" wrote: Chris, You need to use THREED('1:31'!E1:E60) in your SUMPRODUCT formula. THREED isn't standard and you need to get it as an add-in. I believe it's Morefunc available at http://xcell05.free.fr/ v/r -- Brian "chrisk" wrote: I've read through loads of the examples but cant seem to get them to work. Column E has text (a job) column I has a name (JW). there are 31 sheets (one per day) in the workbook. On sheet 32 (just added) i want to total how many jobs JW has done in the month. Tried using =SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW")) but it keeps changing to =SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4)) how do I get around this? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
Here's another site.
http://www.download.com/Morefunc/300...-10423159.html -- Brian "chrisk" wrote: Crashed with 'Fatal Error' when I tried to download it. "Brian" wrote: Chris, You need to use THREED('1:31'!E1:E60) in your SUMPRODUCT formula. THREED isn't standard and you need to get it as an add-in. I believe it's Morefunc available at http://xcell05.free.fr/ v/r -- Brian "chrisk" wrote: I've read through loads of the examples but cant seem to get them to work. Column E has text (a job) column I has a name (JW). there are 31 sheets (one per day) in the workbook. On sheet 32 (just added) i want to total how many jobs JW has done in the month. Tried using =SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW")) but it keeps changing to =SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4)) how do I get around this? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
Try this...
Create these defined names... InsertNameDefine Name: Array1 Refers to: =COLUMN(INDIRECT("A:AE")) Name: Array2 Refers to: =ROW(INDIRECT("1:60")) Then use this formula: =SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Array1&"'!E1:E10"),Array2-1,,))<""),--(T(OFFSET(INDIRECT("'"&Array1&"'!I1:I10"),Array2-1,,))=E4)) -- Biff Microsoft Excel MVP "chrisk" wrote in message ... I've read through loads of the examples but cant seem to get them to work. Column E has text (a job) column I has a name (JW). there are 31 sheets (one per day) in the workbook. On sheet 32 (just added) i want to total how many jobs JW has done in the month. Tried using =SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW")) but it keeps changing to =SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4)) how do I get around this? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
Ooops!
I used the wrong range sizes in the formula, E1:E10 and I1:I10. Change those to E1:E60 and I1:I60. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Create these defined names... InsertNameDefine Name: Array1 Refers to: =COLUMN(INDIRECT("A:AE")) Name: Array2 Refers to: =ROW(INDIRECT("1:60")) Then use this formula: =SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&Array1&"'!E1:E10"),Array2-1,,))<""),--(T(OFFSET(INDIRECT("'"&Array1&"'!I1:I10"),Array2-1,,))=E4)) -- Biff Microsoft Excel MVP "chrisk" wrote in message ... I've read through loads of the examples but cant seem to get them to work. Column E has text (a job) column I has a name (JW). there are 31 sheets (one per day) in the workbook. On sheet 32 (just added) i want to total how many jobs JW has done in the month. Tried using =SUMPRODUCT(--('1:31'!E1:E60="*"),--('1:31'!I1:I60="JW")) but it keeps changing to =SUMPRODUCT(--('1:[31]31'!E1:E60="*"),--('1:[31]31'!I1:I60=E4)) how do I get around this? Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT OR COUNTIF
Hi "T. Valko"
It sort of worked. It definatly counted something, but less than the jobs that they have done. I'd rather a solution along these lines rather than a download. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct & Countif? | Excel Worksheet Functions | |||
Sumproduct and Countif together | Excel Discussion (Misc queries) | |||
COUNTIF or SUMPRODUCT? | Excel Worksheet Functions | |||
countif, sumproduct | New Users to Excel | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |