Formula Help

[ultra429]

I have a formula I've been trying to work on unsuccessfully. I need the formula to count the frequency of the numbers less than 4 in Column L, but based on the text "Woodland Hills" in column C.

I figure this is an array formula, but I have not been able to find anything in my Google searches that combines these two criterion.

Any help is appreciated.

 

[MrKowz]

Welcome to the forums!

If you have Excel 2003 or lower, use:
=SUMPRODUCT(--(L1:L100<4),--(C1:C100="Woodland Hills"))

Excel 2007 or Newer:
=COUNTIFS(L:L,"<4",C:C,"=Woodland Hills")

 

[T. Valko]

I have a formula I've been trying to work on unsuccessfully. I need the formula to count the frequency of the numbers less than 4 in Column L, but based on the text "Woodland Hills" in column C.

I figure this is an array formula, but I have not been able to find anything in my Google searches that combines these two criterion.

Any help is appreciated.

One way...

This will work in ANY version of Excel.

=SUMPRODUCT(--(C2:C10="Woodland Hills"),--(L2:L10 < 4))

Better to use cells to hold the criteria:

• A2 = Woodland Hills
• B2 = 4

Then:

=SUMPRODUCT(--(C2:C10=A2),--(L2:L10<B2))< p> < B2))

If you're using Excel 2007 or later...

=COUNTIFS(C2:C10,A2,L2:L10,"<"&B2)

 

[ultra429]

One way...

This will work in ANY version of Excel.

=SUMPRODUCT(--(C2:C10="Woodland Hills"),--(L2:L10 < 4))


Better to use cells to hold the criteria:

• A2 = Woodland Hills
• B2 = 4

Then:

=SUMPRODUCT(--(C2:C10=A2),--(L2:L10<B2))< p> < B2))

If you're using Excel 2007 or later...

=COUNTIFS(C2:C10,A2,L2:L10,"<"&B2)

Wow this works perfectly. Thank you so much for your help!

 

[T. Valko]

Wow this works perfectly. Thank you so much for your help!

You're welcome. Thanks for the feedback!