# Thread: Sumproduct help Thanks: 0 Likes: 0

1. ## Sumproduct help

Hello

I am trying to accomplish a sumproduct sort of thing and almost have it but not quite yet.

I have cobbled together this formula...

=SUMPRODUCT(IFERROR((G10:G8841>I4)*(H10:H8841="Auto")*(I10:I8841>0),)) array entered.

At issue is the fact that cells in column G may contain numbers (a good thing) or text (a bad thing), How do I ignore the cells that have text or only count the numerics. As it stands now, the text cells are being counted (a bad thing).

Thanks!  Reply With Quote

2. ## Re: Sumproduct help

Will the following formula work for you?

=COUNTIFS(G10:G8841,">"&I4,H10:H8841,"Auto",I10:I8841,">0")  Reply With Quote

3. ## Re: Sumproduct help

Try:

=SUMPRODUCT(IFERROR((G10:G8841+0>I4)*(H10:H8841="Auto")*(I10:I8841>0),))

When you perform an arithmetic function on a number saved as a string, or a regular number, you get a number. If you try to add 0 to a text value, you get an error. In this case, your IFERROR will catch that error and turn it to a 0.  Reply With Quote

4. ## Re: Sumproduct help

=SUMPRODUCT((ISNUMBER(G10:G8841))*(G10:G8841>I4)*(H10:H8841="Auto")*(I10:I8841>0))  Reply With Quote

5. ## Re: Sumproduct help

Perfect! Thank you Originally Posted by Eric W Try:

=SUMPRODUCT(IFERROR((G10:G8841+0>I4)*(H10:H8841="Auto")*(I10:I8841>0),))

When you perform an arithmetic function on a number saved as a string, or a regular number, you get a number. If you try to add 0 to a text value, you get an error. In this case, your IFERROR will catch that error and turn it to a 0.  Reply With Quote

6. ## Re: Sumproduct help

Glad we could help!   Reply With Quote

## User Tag List

bad, cells, sumproduct, text, thing 