Should be easy... substituting > for {1}

[Jennifre]

I am using a formula though need to change part of it to be a >## instead of just showing returns if it IS a certain number.

Here's what I need the formula to be, though the {>29.99} part is giving me the error, "Your formula contains an error", and if I use instead {1} it works fine.

How can I indicate >29.99 in the red characters below? Thanks so much!!

Works:

=SUMPRODUCT(
--($DG$2:$DG$1364=$HF3),
--ISNUMBER(MATCH($DL$2:$DL$1364,{1},0)))/
SUMPRODUCT(
--($DG$2:$DG$1364=$HF3))

Does not work:

=SUMPRODUCT(
--($DG$2:$DG$1364=$HF3),
--ISNUMBER(MATCH($DL$2:$DL$1364,{[COLOR=Red]>29.99[/COLOR]},0)))/
SUMPRODUCT(
--($DG$2:$DG$1364=$HF3))

 

[T. Valko]

I am using a formula though need to change part of it to be a >## instead of just showing returns if it IS a certain number.

Here's what I need the formula to be, though the {>29.99} part is giving me the error, "Your formula contains an error", and if I use instead {1} it works fine.

How can I indicate >29.99 in the red characters below? Thanks so much!!

Works:

=SUMPRODUCT(
--($DG$2:$DG$1364=$HF3),
--ISNUMBER(MATCH($DL$2:$DL$1364,{1},0)))/
SUMPRODUCT(
--($DG$2:$DG$1364=$HF3))

Does not work:

=SUMPRODUCT(
--($DG$2:$DG$1364=$HF3),
--ISNUMBER(MATCH($DL$2:$DL$1364,{>29.99},0)))/
SUMPRODUCT(
--($DG$2:$DG$1364=$HF3))

Do you want to test the range to see if it contains the literal string >29.99 or do you want to test the range to see if contains numeric values greater than 29.99?

 

[Jennifre]

Well... I need to find numeric values greater than or equal to 30 if that's possible. Or greater than 29.99. But NOT the literal ">29.99".

Thanks, T.Valko

 

[T. Valko]

Well... I need to find numeric values greater than or equal to 30 if that's possible. Or greater than 29.99. But NOT the literal ">29.99".

Thanks, T.Valko

OK, try it like this...

=SUMPRODUCT(--($DG$2:$DG$1364=$HF3),--($DL$2:$DL$1364>=30))/COUNTIF(
$DG$2:$DG$1364,$HF3)

If you're using Excel 2007 or later:

=COUNTIFS($DG$2:$DG$1364,$HF3,$DL$2:$DL$1364,">=30")/COUNTIF(
$DG$2:$DG$1364,$HF3)

 

[Jennifre]

That works great, thank you so much !! : - )

 

[T. Valko]

That works great, thank you so much !! : - )

You're welcome. Thanks for the feedback!