SumProduct By Month with #N/A Problem

**trsmith** · 06-16-2009, 11:14 AM

I need a little help to finish this out, this below works fine as long as I don't have any #N/A (theses are Formulas #N/A). I'm not sure what to place to ignore the #N/A and just bring back the the info I need.

What I'm doing with this is take the date the customer made an order that month and seeing the average of how many days it took in that month to get the order shipped.

I have seen iserror and other ones but I'm not sure how to place this and still get it to work. This is what I have so far.

=SUMPRODUCT(--(MONTH($D$9:$D$698)=4),$J$9:$J$698)/SUMPRODUCT(--(MONTH($D$9:$D$698)=4))

Thanks ahead for anyone who spends any time on this!
Travis

**JBeaucaire** · 06-16-2009, 11:17 AM

Can you post the formulas that are creating the N/A# errors in your sheet? The best solution would involve solving THOSE errors so your sumproduct() formula can work.

**DonkeyOte** · 06-16-2009, 11:22 AM

Ignoring issues of #N/A it might make sense to switch to an AVERAGE Array rather than 2 Sumproducts:

=AVERAGE(IF(MONTH($D$9:$D$698)=4,$J$9:$J$698))
committed with CTRL + SHIFT + ENTER

if the NAs are in J you could use:

=AVERAGE(IF((MONTH($D$9:$D$698)=4)*(ISNUMBER($J$9:$J$698)),$J$9:$J$698))
committed with CTRL + SHIFT + ENTER
(can adapt for D if necessary)

but as JB states always better to fix the issue at source - ie resolve NA's wherever they are appearing.

**trsmith** · 06-16-2009, 11:23 AM

The #N/A are items that have not shipped yet and is pulling from an index array. I pull info out of our company system in a paste into different tabs and I have all this pulled into one tab. This is do I can update the tabs with a simple copy paste. And this will update the main tab. I will always have some #N/A.