Error problem

[Ayo]

I have this formular:
=SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes"))

but it is showing as an error. Iknow there is no errors in there but when I
click the Trace Error option I get a little "Table" icon and I don't know
what that is or what it means.
Any ideas?

 

[Mike H]

Hi,

There's nothing wrong with the formula so it must be the data, what 'error'
are you getting?

Mike

 

[Mike H]

Hi,

There's nothing wrong with the formula so it must be the data, what 'error'
are you getting?

Mike

 

[Eduardo]

Hi,
the missing part in the formula is where you want to pull the information if
the three conditions are met, let's say column F

=SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes"),Tables!$F$2:$F$2979)

 

[Eduardo]

Hi,
the missing part in the formula is where you want to pull the information if
the three conditions are met, let's say column F

=SUMPRODUCT(--(Tables!$C$2:$C$2979=$A4),--(Tables!$D$2:$D$2979=$B4),--(Tables!$E$2:$E$2979="Yes"),Tables!$F$2:$F$2979)

 

[Ayo]

I get the #N/A

 

[Ayo]

I get the #N/A

 

[Dave Peterson]

Look for error(s) in one (or all) of those ranges.

Remember to look in hidden rows, too.

 

[Dave Peterson]

Look for error(s) in one (or all) of those ranges.

Remember to look in hidden rows, too.

 

[Ayo]

No hidden rows and no links to other tables, which was my first suspision

 

[Ayo]

No hidden rows and no links to other tables, which was my first suspision

 

[Dave Peterson]

Did you look for errors in any of those ranges?

No hidden rows and no links to other tables, which was my first suspision

 

[Dave Peterson]

Did you look for errors in any of those ranges?

No hidden rows and no links to other tables, which was my first suspision

 

[Ayo]

I have been going over and over all of the data and I can't find anything. I
tried the help function in excel and I learned that when I see "a black arrow
points from the selected cell to a worksheet icon" that the cell "is
referenced by a cell on another worksheet or workbook". I checked and there
are no references. I deleted all the references in the Name Manager also.

 

[Ayo]

I have been going over and over all of the data and I can't find anything. I
tried the help function in excel and I learned that when I see "a black arrow
points from the selected cell to a worksheet icon" that the cell "is
referenced by a cell on another worksheet or workbook". I checked and there
are no references. I deleted all the references in the Name Manager also.

 

[Dave Peterson]

Are you sure that the formula you posted is what was used in the cell?

Your formula doesn't really look like:
=table(x1,y1)
does it?

I have been going over and over all of the data and I can't find anything. I
tried the help function in excel and I learned that when I see "a black arrow
points from the selected cell to a worksheet icon" that the cell "is
referenced by a cell on another worksheet or workbook". I checked and there
are no references. I deleted all the references in the Name Manager also.

 

[Dave Peterson]

Are you sure that the formula you posted is what was used in the cell?

Your formula doesn't really look like:
=table(x1,y1)
does it?

I have been going over and over all of the data and I can't find anything. I
tried the help function in excel and I learned that when I see "a black arrow
points from the selected cell to a worksheet icon" that the cell "is
referenced by a cell on another worksheet or workbook". I checked and there
are no references. I deleted all the references in the Name Manager also.

 

[Ayo]

This is exactly what is in the cell:
=SUMPRODUCT(--(Tables!$C$2:$C$2958=$A4),--(Tables!$D$2:$D$2958=$B4),--(Tables!$E$2:$E$2958="Yes"))

 

[Ayo]

This is exactly what is in the cell:
=SUMPRODUCT(--(Tables!$C$2:$C$2958=$A4),--(Tables!$D$2:$D$2958=$B4),--(Tables!$E$2:$E$2958="Yes"))

 

[David Biddulph]

So, if you are getting #N/A as an answer, you have #N/A in at least one of
the cells referenced in your formula. The table icon means, as help told
you, "that the cell "is referenced by a cell on another worksheet or
workbook". ". You therefore need to go to the worksheet named "Tables", and
on that sheet you need to look in the range C2:E2958, as it is in that range
that you have at least one cell containing the N/A error.

You can find which row(s) the error is in by putting the formula
=ISNA(SUM(C2:E2)) in a spare cell in row 2, and copying down, and similarly
you can find out which column(s) by putting =ISNA(SUM(C2:C2958)) in a spare
cell in column C, and copying across.
--
David Biddulph

This is exactly what is in the cell:
=SUMPRODUCT(--(Tables!$C$2:$C$2958=$A4),--(Tables!$D$2:$D$2958=$B4),--(Tables!$E$2:$E$2958="Yes"))