use sum product on cells that contain formulas

[ClaireC]

I have the current formula to use as a count based on 2 criteria.
=SUMPRODUCT((TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$L$13,"m/dd/yy"))*(Order!H2:H65535>=1)*(Order!A2:A65535<>"")*(Order!H2:H65535<>""))

Column A on my Order sheet is an entered date and L13 is a specific date criteria - this works fine on its own
Column H on my Order sheet is a calculated value (cells contain a formula) - this part does not work

I have tried changing the "*" to "," as I have seen suggested elsewhere but this does not work either. I believe that the SUMPRODUCT function is having trouble with the H column because it contains a formula and not an entered value. Does anyone know how to make this work or another way to accomplish the desired outcome? Any help is greatly appreciated.

 

[Norie]

Whether a value is entered or the result of a formula should make no difference, formulas only look at the value.

What is the formula in column H?

 

[ClaireC]

This is the formulas in column H
=IF(F2=0,0,IF(((D2-G2)*15)>F2,F2,((D2-G2)*15)))

The formula in my original post results in #N/A, in case that helps.

 

[Aladin Akyurek]

I have the current formula to use as a count based on 2 criteria.
=SUMPRODUCT((TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$L$13,"m/dd/yy"))*(Order!H2:H65535>=1)*(Order!A2:A65535<>"")*(Order!H2:H65535<>""))

Column A on my Order sheet is an entered date and L13 is a specific date criteria - this works fine on its own
Column H on my Order sheet is a calculated value (cells contain a formula) - this part does not work

I have tried changing the "*" to "," as I have seen suggested elsewhere but this does not work either. I believe that the SUMPRODUCT function is having trouble with the H column because it contains a formula and not an entered value. Does anyone know how to make this work or another way to accomplish the desired outcome? Any help is greatly appreciated.

Looks like...

=SUMPRODUCT(
  --(TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$L$13,"m/dd/yy")),
  --ISNUMBER(Order!H2:H65535),
  --(Order!H2:H65535>=1))

 

[ClaireC]

Looks like...

=SUMPRODUCT(
  --(TEXT(Order!A2:A65535,"m/dd/yy")=TEXT('Order Confirmation'!$L$13,"m/dd/yy")),
  --ISNUMBER(Order!H2:H65535),
  --(Order!H2:H65535>=1))

Thank you for the rewrite but this also results in #N/A.

 

[Aladin Akyurek]

Thank you for the rewrite but this also results in #N/A.

Do you have formulas in Order!H2:H65535 which could return #N/A like one with VLOOKUP?

 

[ClaireC]

Do you have formulas in Order!H2:H65535 which could return #N/A like one with VLOOKUP?

Thanks for the tip! It was actually one of the other columns on the Order sheet that had formula results of #N/A. I fixed it and now the count is working.

 

[Aladin Akyurek]

Thanks for the tip! It was actually one of the other columns on the Order sheet that had formula results of #N/A. I fixed it and now the count is working.

Great. Thanks for providing feedback.