# Multiple COUNTIF formula that ignores #N/A

#### Fin Fang Foom

##### Well-known Member
I'm trying to get this formula to work. I have a multiple count formula but I'm still getting #N/A errors how can I ignore them ?

This what I have so far:

=SUMPRODUCT((ISNA(Sheet1!B9:B100=F2))*(Sheet1!C9:AG100>0))

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Don't you need an If(isna... somewhere?

Like: If(isna(sumproduct(....),"",sumproduct(....))?

Fin Fang Foom said:
I'm trying to get this formula to work. I have a multiple count formula but I'm still getting #N/A errors how can I ignore them ?

This what I have so far:

=SUMPRODUCT((ISNA(Sheet1!B9:B100=F2))*(Sheet1!C9:AG100>0))

Since there is no problem description available, a guess is due:

=SUM(IF(ISNUMBER((Sheet1!B9:B100=F2)+0),(Sheet1!C9:AG100>0)+0))

which must be confirmed with control+shift+enter.

I just can't get it to work.

I used your formula and still get a #N/A error.

Look in cell I10 the answer suppose to be 2. Because he worked 2 days.

And cell I2 is a data validation list.

=SUM(IF(ISNUMBER((Sheet1!B9:B100=F2)+0),(Sheet1!C9:AG100>0)+0))

bump

Try...

=SUM(IF(B3:B8=I2,IF(ISNUMBER(C3:H8),(C3:H8>0)+0)))

...confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

Once again Domenic Thank You very much. ray:

Replies
3
Views
185
Replies
1
Views
249
Replies
4
Views
181
Replies
5
Views
212
Replies
7
Views
104

1,206,718
Messages
6,074,500
Members
446,072
Latest member
OrangeYellow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back