index match & NA

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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

DQ
 
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
 
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
 
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.
 
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
 
Back
Top