How can I enhance this SUMPRODUCT formula to only counts cells greater than zero?

Hi all,

I have the following formula:

=SUMPRODUCT(($B4:$R4)*(1/$B3:$R3)*($B$1:$R$1=$B$8))/COUNTIFS($B$1:$R$1,$B$8)

..where:

- Row 1 is a date range, and I only want cells from rows 3 and 4 to be included if a date condition is met
- Rows 3 and 4 are data, with row 4 being divided by row 3 when that date condition is met
- Cell B8 is where I input the date condition value

- Finally, the COUNTIFS at the end produces an average

However, my data rows 3 and 4 will sometimes contain #N/A values. I need things that way as I'm producing graphs from the data too and don't want the data points to show up there. The problem is that these cells also stop the formula from working on cells where data is present.

The first tab shows this working. The second shows it when it doesn't.

Can you help me add a condition where it will only count cells greater than zero? If a column in row 3 has an #N/A then so should the column in row 4, meaning that the error checking should only be needed once.

Thanks in advance as always for your expertise!

SUMPRODUCT Question.xlsx

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

1. Are you still using Excel 2007? If not, please update your profile.

2. In the absence of a sheet to play with... one wild guess.

=SUMPRODUCT((IFERROR($A3:$Z3,0))*(IFERROR(1/$A2:$Z2,0))*($A$1:$Z$1=$A$10))/COUNTIFS($A$1:$Z$1,$A$10)

If still using Excel 2007 (or anything pre-O365), this will need to be set with CTRL-SHIFT-ENTER instead of enter, before dragging.

So, I put in 2 #N/A values and amended the formula to:

=SUMPRODUCT((IF(ISERROR($B4:$R4),0,$B4:$R4))*(IFERROR(1/$B3:$R3,0))*($B$1:$R$1=$B$8))/COUNTIFS($B$1:$R$1,$B$8)

does thiswork for you?

There is NO second Tab!!!

A change of approach:

=LET(A,B1:R1,b,B3:R3,C,B4:R4,AVERAGE(TOROW(FILTER(C,A=B8),3)/TOROW(FILTER(b,A=B8),3)))

Please explain WHY the answer should be anything OTHER than 24.77918 (manually calculated in orange cells).

Fair enough. I'll step back at this point. I changed my approach, because my original one didn't work. I could not see how you got your expected results.

Over to others.

Try this:

=SUMPRODUCT((IF(ISERROR($B4:$R4),0,$B4:$R4))*(IFERROR(1/$B3:$R3,0))*($B$1:$R$1=$B$8))/SUMPRODUCT((ISNUMBER($B4:$R4))*(ISNUMBER($B3:$R3)*($B$1:$R$1=$B$8)))

This works! Thank you so much, I really appreciate it.

I'll add a thanks to the both of you for your time on this. Have fantastic Easter weekends.

You, too.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Or try this shorter formula:

This formula returns also the expected result of 24.5803223741788:

Formula:

Please Login or Register  to view this content.

Thanks for the feedback and rep . Glad to have helped.