Sumproduct problem with blanks / text

[plant007]

Hello

I am working on a table where I am using sumproduct but do not want to count blank cells or cells with text in (column G) I have used the following equations;

=IF(OR(ISBLANK($G$3:$G$15),ISTEXT($G$3:$G$15)),,SUMPRODUCT(--($G$3:$G$15<=$D$3:$D$15)))

which returns 9

and

=IF(ISNUMBER($G$3:$G$15),SUMPRODUCT(--($G$3:$G$15<=$D$3:$D$15)--($G$3:$G$15<>"")))

which returns FALSE

The answer should be 5 as there are 5 examples where the date in column G is the same or less than the corresponding date in column D

Any help would be most appreciated
Thanks
Any

 

[Aladin Akyurek]

Hello

I am working on a table where I am using sumproduct but do not want to count blank cells or cells with text in (column G) I have used the following equations;

=IF(OR(ISBLANK($G$3:$G$15),ISTEXT($G$3:$G$15)),,SUMPRODUCT(--($G$3:$G$15<=$D$3:$D$15)))

which returns 9

and

=IF(ISNUMBER($G$3:$G$15),SUMPRODUCT(--($G$3:$G$15<=$D$3:$D$15)--($G$3:$G$15<>"")))

which returns FALSE

The answer should be 5 as there are 5 examples where the date in column G is the same or less than the corresponding date in column D

Any help would be most appreciated
Thanks
Any

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER($G$3:$G$15),IF($G$3:$G$15<=$D$3:$D$15,1)))

 

[Jonmo1]

You're almost there..

Try

=SUMPRODUCT(--($G$3:$G$15<=$D$3:$D$15),--($G$3:$G$15<>""))

 

[plant007]

Thanks to both of you for your kind assistance and I can now use your help to build upon in my work

Jonmo1 - I get an answer of 6 with your formula as I think it is picking up examples where text is te same in both columns (in this case "N/A")

 

[Aladin Akyurek]

Thanks to both of you for your kind assistance and I can now use your help to build upon in my work

Jonmo1 - I get an answer of 6 with your formula as I think it is picking up examples where text is te same in both columns (in this case "N/A")

You are welcome. Thanks for providing us feedback. IsNumber is a better test for the existence of numbers.