index match & NA
 

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

index match & NA
 

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

index match & NA
 

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

index match & NA
 

Try this:

=IF(AND(COUNTIF('sheet1'!$A$1:$A$400,"Mike"),SUM(C OUNTIF('sheet1'!$A$1:$AP$1,{"trinket","widget"}))) ,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)),"the criteria you're searching is not on the list")




"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

index match & NA
 

Post the *exact* formula you tried. Toppers formula should work. If "Mike"
or "trinket" or "widget" does not exist the formula will simply return 0.

Are there any errors in the range?

Biff

"denise" wrote in message
...
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

index match & NA
 

Denise,

I think Toppers fromula will work if you press Ctrl+Shift+Enter after
entering it.

DQ

index match & NA
 

I think Toppers fromula will work if you press Ctrl+Shift+Enter after
entering it.

Not necessary. It'll work normally entered.

Biff

"dq" wrote in message
oups.com...
Denise,

I think Toppers fromula will work if you press Ctrl+Shift+Enter after
entering it.

DQ

index match & NA
 

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

index match & NA
 

OK, part of the problem may be with the actual title of the trinket. This is
the exact formula:
=SUMPRODUCT(('1'!$A$2:$A$400="mike")*(('1'!$B$1:$A P$1="$29.99 1-yr contract
trinket")+('1'!$B$1:$AP$1="$35 no contract widget"))*('1'!$B$2:$AP$400))

The referenced sheet refers to the day of the month, 1 through 31. When I
simplify the spreadsheet as a test and use the above formula, I get Mike's
widget results but no trinkets, otherwise I get a 0.

When I use the same formula in my full spreadsheet, I see a #VALUE error any
time data is on the referenced sheet. If the sheet is blank (such as on a
Sunday), the result does show as 0.

I'm sorry, I can't send the actual spreadsheet due to company restrictions.

Thanks for helping figure this out!

denise

"Toppers" wrote:

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

index match & NA
 

Denise,
I tried your posted formula (with spreadsheet named "1") and
it worked fine with several combinations of data/headings. I assume the data
in B2:AP400 is numeric so I equally assume the #VALUE is a data problem

Unless I have the actual data or something which replicates it, I can't help
any further.
"denise" wrote:

OK, part of the problem may be with the actual title of the trinket. This is
the exact formula:
=SUMPRODUCT(('1'!$A$2:$A$400="mike")*(('1'!$B$1:$A P$1="$29.99 1-yr contract
trinket")+('1'!$B$1:$AP$1="$35 no contract widget"))*('1'!$B$2:$AP$400))

The referenced sheet refers to the day of the month, 1 through 31. When I
simplify the spreadsheet as a test and use the above formula, I get Mike's
widget results but no trinkets, otherwise I get a 0.

When I use the same formula in my full spreadsheet, I see a #VALUE error any
time data is on the referenced sheet. If the sheet is blank (such as on a
Sunday), the result does show as 0.

I'm sorry, I can't send the actual spreadsheet due to company restrictions.

Thanks for helping figure this out!

denise

"Toppers" wrote:

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

index match & NA
 

Thanks to everyone for helping me with this. I've come up with a different
approach where I won't add 2 products together until after the fact. That
pretty much does away with the problem I was having.

Again, thanks much!
Denise

"Toppers" wrote:

Denise,
I tried your posted formula (with spreadsheet named "1") and
it worked fine with several combinations of data/headings. I assume the data
in B2:AP400 is numeric so I equally assume the #VALUE is a data problem

Unless I have the actual data or something which replicates it, I can't help
any further.
"denise" wrote:

OK, part of the problem may be with the actual title of the trinket. This is
the exact formula:
=SUMPRODUCT(('1'!$A$2:$A$400="mike")*(('1'!$B$1:$A P$1="$29.99 1-yr contract
trinket")+('1'!$B$1:$AP$1="$35 no contract widget"))*('1'!$B$2:$AP$400))

The referenced sheet refers to the day of the month, 1 through 31. When I
simplify the spreadsheet as a test and use the above formula, I get Mike's
widget results but no trinkets, otherwise I get a 0.

When I use the same formula in my full spreadsheet, I see a #VALUE error any
time data is on the referenced sheet. If the sheet is blank (such as on a
Sunday), the result does show as 0.

I'm sorry, I can't send the actual spreadsheet due to company restrictions.

Thanks for helping figure this out!

denise

"Toppers" wrote:

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