|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 25th, 2010, 05:23 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT WITH 4 CONDITIONS NOT WORKING
My formulae below is not working, can you help?
Column H contains 'NZ' or 'SZ', column I is in 20/5/2010 format, column G contains 'trade' or 'fdi', Column E is a list in rows 4 to 16 incl containing text statements. =SUMPRODUCT(--('outputs detail'!$H$4:$H$999="NZ"),--(MONTH('outputs detail'!$I$4:$I$999)=5),--('outputs detail'!$G$4:$G$999="trade"),--('outputs detail'!$B$4:$B$999='outputs detail'!$E$4)) -- any help gratefully received thanks carrach 
|
|
#2
May 25th, 2010, 05:52 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT WITH 4 CONDITIONS NOT WORKING
Column E is a list in rows 4 to 16 incl containing text statements.
Replace this: --('outputs detail'!$B$4:$B$999='outputs detail'!$E$4) With this: --(ISNUMBER(MATCH('outputs detail'!$B$4:$B$999,'outputs detail'!$E$4:$E$16,0))) -- Biff Microsoft Excel MVP "Carrach" wrote in message ... My formulae below is not working, can you help? Column H contains 'NZ' or 'SZ', column I is in 20/5/2010 format, column G contains 'trade' or 'fdi', Column E is a list in rows 4 to 16 incl containing text statements. =SUMPRODUCT(--('outputs detail'!$H$4:$H$999="NZ"),--(MONTH('outputs detail'!$I$4:$I$999)=5),--('outputs detail'!$G$4:$G$999="trade"),--('outputs detail'!$B$4:$B$999='outputs detail'!$E$4)) -- any help gratefully received thanks carrach
|
|
#3
May 27th, 2010, 09:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT WITH 4 CONDITIONS NOT WORKING
Hi Biff,
Thank you for the help, however I think I may have had something incorrect to start with: working on excel 2003 In the summary sheet I have : headings, each heading is split into trade or FDI, each trade & FDI is split into NZ or SZ, and each of those is only counted for the relevant month. NZ SZ Businesses assisted Trade 15 1 FDI 4 0 Total 19 1 Jobs Created Trade 1 0 FDI 15 0 Total 16 0 Each of these is in a seperate column in a different sheet but in the same workbook so the sum is: if sheet2 col1=business assist, and sheet2 col2=trade, and sheet2 col3 = NZ and sheet2 col 4 month=5, then count the rows. And: if sheet2 col1 = jobs created, and sheet2 col2=trade, and sheet2 col3 = NZ and sheet2 col 4 month=5, then sum the values in col 6 Is that clearer?? -- any help gratefully received thanks carrach "T. Valko" wrote: Column E is a list in rows 4 to 16 incl containing text statements. Replace this: --('outputs detail'!$B$4:$B$999='outputs detail'!$E$4) With this: --(ISNUMBER(MATCH('outputs detail'!$B$4:$B$999,'outputs detail'!$E$4:$E$16,0))) -- Biff Microsoft Excel MVP "Carrach" wrote in message ... My formulae below is not working, can you help? Column H contains 'NZ' or 'SZ', column I is in 20/5/2010 format, column G contains 'trade' or 'fdi', Column E is a list in rows 4 to 16 incl containing text statements. =SUMPRODUCT(--('outputs detail'!$H$4:$H$999="NZ"),--(MONTH('outputs detail'!$I$4:$I$999)=5),--('outputs detail'!$G$4:$G$999="trade"),--('outputs detail'!$B$4:$B$999='outputs detail'!$E$4)) -- any help gratefully received thanks carrach .
|
|
#4
May 28th, 2010, 03:07 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT WITH 4 CONDITIONS NOT WORKING
Is that clearer??
Well, unfortunately, no it's not. I would need to see how your data is setup. If you can post a sample file somewhere I'll take a look at it. -- Biff Microsoft Excel MVP "Carrach" wrote in message ... Hi Biff, Thank you for the help, however I think I may have had something incorrect to start with: working on excel 2003 In the summary sheet I have : headings, each heading is split into trade or FDI, each trade & FDI is split into NZ or SZ, and each of those is only counted for the relevant month. NZ SZ Businesses assisted Trade 15 1 FDI 4 0 Total 19 1 Jobs Created Trade 1 0 FDI 15 0 Total 16 0 Each of these is in a seperate column in a different sheet but in the same workbook so the sum is: if sheet2 col1=business assist, and sheet2 col2=trade, and sheet2 col3 = NZ and sheet2 col 4 month=5, then count the rows. And: if sheet2 col1 = jobs created, and sheet2 col2=trade, and sheet2 col3 = NZ and sheet2 col 4 month=5, then sum the values in col 6 Is that clearer?? -- any help gratefully received thanks carrach "T. Valko" wrote: Column E is a list in rows 4 to 16 incl containing text statements. Replace this: --('outputs detail'!$B$4:$B$999='outputs detail'!$E$4) With this: --(ISNUMBER(MATCH('outputs detail'!$B$4:$B$999,'outputs detail'!$E$4:$E$16,0))) -- Biff Microsoft Excel MVP "Carrach" wrote in message ... My formulae below is not working, can you help? Column H contains 'NZ' or 'SZ', column I is in 20/5/2010 format, column G contains 'trade' or 'fdi', Column E is a list in rows 4 to 16 incl containing text statements. =SUMPRODUCT(--('outputs detail'!$H$4:$H$999="NZ"),--(MONTH('outputs detail'!$I$4:$I$999)=5),--('outputs detail'!$G$4:$G$999="trade"),--('outputs detail'!$B$4:$B$999='outputs detail'!$E$4)) -- any help gratefully received thanks carrach .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
