How do I COUNTIF C2:C260<20, AND value_in_column_I=5 ?

[MrNewBee]

I'm sure this is simple, but being a complete newbie, I need a little formula help. I have inherited a workbook and need to chart three values from column I, where the values in Column C is <20. I've tried variations of COUNTIF and IF(COUNTIF, and can't work it out, and I'm hitting a deadline. Thanks in advance for any suggestions.
Mr. NewBee :wink:

More details: Basically I need three formulas, so I can chart this, one where column I value is 5, one where it is 4 and one where it is 3, and the values in column C are less than 20. Column C is numbers 0-100. Column C is only 3-5.

 

[Oaktree]

Welcome to the board.

Try:

=SUMPRODUCT(--(C2:C260<20),--(I2:I260=5))

 

[MrNewBee]

Wonderful. I would never have thought that SUMPRODUCT would get me where I was going. That works excellent! Thank you!

 

[MrNewBee]

Hmmm.. Looks like I spoke too soon. It worked on my small example with only values, but it doesn't seem to work on the real sheet?

I have a sheet which is a large table full of formulas and numbers. The number evaluations are actually created by use of a complex formula using VLOOKUP at it's heart.

 

[MrNewBee]

OH, I got it. The 5,4,3 column was numbers, formatted as text. I had to convert it to actual numbers (duh). Thanks!