Amend SumProduct formula to exclude #N/A values?

[Ironman]

Hi

I have a row of data with this formula

=SUMPRODUCT((ISNUMBER($A$2:$A$257))*(B2:B257=0))

The formula sums all cells in the column containing a zero value. However, this no longer works after formulas were inserted in the sheet resulting in a #N/A value.

Is there any way the above formula can be amended so it ignores the #N/A values please?

Many thanks!

 

[Dave Patton]

A concise example would help. You can post an extract of your sheet with the forum's tool named XL2BB.
What column has the N/A?

Can you edit the formula to show 0 instead of N/A?

 

[Ironman]

Hi Dave, thanks for replying.

The formula value must contain a #N/A value as it's chart-related, to avoid a string of zeros for future values that I don't want to see (which it used to do before the formula changed).

Below is an extract from the sheet. For some reason I get a VB error when I tried to copy as Mini Sheet, so it's a table.

1	0
RK/39	31
YTD AVE	0
RK/39	31
2	0
RK/39	#N/A
YTD AVE	0
RK/39	#N/A
3	0
RK/39	#N/A
YTD AVE	0
RK/39	#N/A
4	0
RK/39	#N/A
YTD AVE	0
RK/39	#N/A
5	0
RK/39	#N/A
YTD AVE	0
RK/39	#N/A

The blank green rows are those containing the #N/A formula. They are Rows 3, 5, 8, 10, 13, 15, 18, 20, 23, 25, 28 and so on (you see the pattern), down to row 258.

Hope that helps.

 

[Dave Patton]

You didn't post the Column and Row information.
Did you post mini sheet?
What result do you expect with the above?
How did you hide the N/As?

 

[Dave Patton]

Is this what your data is like?
Is your expected answer 4? If not, how did you calculate the expected answer.

SumProduct2022a.xlsm
	A	B	C
1
2	1	0	4
3	RK/39	31
4	YTD AVE	0
5	RK/39	31
6		#N/A
7	2	0
8	RK/39	#N/A
9	YTD AVE	0
10	RK/39	#N/A
11		#N/A
12	3	0
13	RK/39	#N/A
14	YTD AVE	0
15	RK/39	#N/A
16
17	4	0
18	RK/39	#N/A
19	YTD AVE	0
20	RK/39	#N/A
21
22	5	0
23	RK/39	#N/A
24	YTD AVE	0
25	RK/39	#N/A
7a

Cell Formulas
Range	Formula
C2	C2	=COUNTIFS(B2:B31,0,A2:A31,">0")
B6,B11,B8	B6	=VLOOKUP(G6,H3:H6,1,0)

 

[Ironman]

For some reason I get a VB error when I tried to copy as Mini Sheet, so it's a table.

The green cells are those containing the N/A and are hidden using Conditional Formatting.

In your example above, the expected result would be 5, because Rows 2, 7, 12, 17 and 22 all contain zeros.

I'm happy to upload an extract of the sheet if it helps you?

 

[Dave Patton]

One of the "numbers" was TEXT.

SumProduct2022a.xlsm
	A	B	C
1			5
2	1	0
3	RK/39	31
4	YTD AVE	0
5	RK/39	31
6		#N/A
7	2	0
8	RK/39	#N/A
9	YTD AVE	0
10	RK/39	#N/A
11		#N/A
12	3	0
13	RK/39	#N/A
14	YTD AVE	0
15	RK/39	#N/A
16
17	4	0
18	RK/39	#N/A
19	YTD AVE	0
20	RK/39	#N/A
21
22	5	0
23	RK/39	#N/A
24	YTD AVE	0
25	RK/39	#N/A
7a

Cell Formulas
Range	Formula
C1	C1	=COUNTIFS(B2:B31,0,A2:A31,">0")
B6,B11,B8	B6	=VLOOKUP(G6,H3:H6,1,0)

 

[Ironman]

Thanks Dave - that's brilliant, it works - I love the way you thought that out by looking at the values in Col A >0 as a second condition, which therefore ignored the YTD AVE 0's from the calculation!