Sumproduct help

[SteveH1961]

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!

 

[Tetra201]

Will the following formula work for you?

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

 

[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.

 

[DanteAmor]

Continuing with your formula

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

 

[SteveH1961]

Perfect! Thank you

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.

 

[Eric W]

Glad we could help!