Countifs Question

[Pezgordo]

I am trying to determine that if a number in column DG is > than the number in column IE, what are the number of O's and/or U's in column JD? O and U are the letters being used in column JD. I tried the following formulas but keep getting an error:

=countifs(dg7:dg2870>ie7:ie2870,jd7:jd2870,"o")

=countifs(dg7:dg2870,">",ie7:ie2870,jd7:jd2870,"o")

 

[Aladin Akyurek]

I am trying to determine that if a number in column DG is > than the number in column IE, what are the number of O's and/or U's in column JD? O and U are the letters being used in column JD. I tried the following formulas but keep getting an error:

=countifs(dg7:dg2870>ie7:ie2870,jd7:jd2870,"o")

=countifs(dg7:dg2870,">",ie7:ie2870,jd7:jd2870,"o")

Try one of...

=SUMPRODUCT(
    --ISNUMBER(DG7:DG2870),
    --(DG7:DG2870 > IE7:IE2870),
    --(JD7:JD2870="o"))

=SUMPRODUCT(
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--ISNUMBER(MATCH(JD7:JD2870,{"o","u"},0)))

 

[Pezgordo]

Code:

=SUMPRODUCT(
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--(JD7:JD2870="o"))

This one worked perfectly. Thank you.

How would I add column F (which has letters H and A in the column) to the beginning of the formula?

 

[Robert Mika]

Try:

=SUMPRODUCT(--ISNUMBER(MATCH(F7:F2870,{"a","h"},0)),
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--ISNUMBER(MATCH(JD7:JD2870,{"o","u"},0)))

 

[Aladin Akyurek]

Code:

=SUMPRODUCT(
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--(JD7:JD2870="o"))

This one worked perfectly. Thank you.

How would I add column F (which has letters H and A in the column) to the beginning of the formula?

If column F must be tested separately for H and A...

=SUMPRODUCT(
--(F7:F2870="H"),
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--(JD7:JD2870="o"))

If both for H and A together in an OR structure...

=SUMPRODUCT(
--ISNUMBER(MATCH(F7:F2870,{"H","A"),0)),
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--(JD7:JD2870="o"))

 

[Pezgordo]

Thanks guys, they worked great.

=SUMPRODUCT(
--(F7:F2870="H"),
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--(JD7:JD2870="o"))

This one worked well for what I was trying to do.

 

[Aladin Akyurek]

Thanks guys, they worked great.

=SUMPRODUCT(
--(F7:F2870="H"),
--ISNUMBER(DG7:DG2870),
--(DG7:DG2870 > IE7:IE2870),
--(JD7:JD2870="o"))

This one worked well for what I was trying to do.

You are welcome. Thanks for providing us feedback.