index match & NA

[Guest]

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",'sheet1'!$A$1:$A$400,0),MATCH("widget",'sheet1'!$A$1:$AP$1,0))

Thanks,
Denise

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Try this:

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

 

[T. Valko]

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

 

[dq]

Denise,

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

DQ

 

[T. Valko]

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

entering it.

Not necessary. It'll work normally entered.

Biff

 

[Guest]

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

 

[Guest]

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:$AP$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

 

[Guest]

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.

 

[Guest]

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