It does work as supplied and does not require Ctrl+Shift+Enter at entry. (I
ran several tests before posting). And as replies from Biff state, if no
conditions exist, you will get 0 as a result.

To repeat Biff's request: please post your exact formula. To re-affirm: the
data is formatted as below with the numeric data in B2 to C4 in the example
below. A1 is empty.


Col A Col B Col C

Widget Trinket <====Row 1
John 1 2
Mike 3 4
Fred 5 6

If you want post w/book to toppers at REMOVETHISjohntopley.fsnet.co.uk

"denise" wrote:

Hi Toppers,

Well I just keep getting a #value error with your suggestion. I've tried it
by referencing a sheet where I know the result should be 9 and also with a
sheet where Mike does not appear although the trinket and widget columns do.
Both give me the value error.

The reason I need to add the isna is because sometimes mike will have
results for a widget, sometimes a trinket, sometimes both, sometimes none,
and sometimes mike won't even be on the daily list so it's pretty much an
either/or situation.

Thanks,
Denise

"Toppers" wrote:

Try this an alternative, NO ISNA needed:

Assuming "names" in column A (A2 onwards) and "goods" in row, column B onwards

=SUMPRODUCT((Sheet1!$A$2:$A$400="mike")*((Sheet1!$ B$1:$AP$1="widget")+(Sheet1!$B$1:$AP$1="trinket")) *(Sheet1!$B$2:$AP$400))

HTH

"denise" wrote:

Hi Folks,

The formula listed below works great but now I need to include an "if isna"
statement for those incidents where Mike may not have any trinkets or widgets
to sum. I'm hoping someone can help me re-write this so it there will be
fewer characters and I can fit in the "if isna" statement.

=INDEX('sheet1'!$A$1:$AP$400,MATCH("mike",'sheet1' !$A$1:$A$400,0),MATCH("trinket",'sheet1'!$A$1:$AP$ 1,0)))+INDEX('sheet1'!$A$1:$AP$400,MATCH("mike",'s heet1'!$A$1:$A$400,0),MATCH("widget",'sheet1'!$A$1 :$AP$1,0))

Thanks,
Denise