SUMPRODUCT WITH 4 CONDITIONS NOT WORKING

[Carrach]

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))

 

[T. Valko]

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)))

 

[Carrach]

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??

 

[T. Valko]

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.