IF THEN across multiple pages, for multiple values

[mleunga]

Hello,

I am having trouble writing a formula for the following:

I have values in cells D2:D5 in Sheet 2. If the number is >99 in each cell, then I would like a value of 1 to be assigned. If it is <99, I would like the value to be 0. I would like to count these individually (i.e. line by line) to come up with a total value in cell C4 in Sheet 1. As I show using the example below, the value in cell C4 in Sheet 1 should be 2 as only 2 values meet the IF THEN criteria. Subsequently, if the value changes in cell D4 in Sheet 2 to 100, cell C4 in Sheet 1 would automatically update to be 3.

Sheet 2:
D2=100 --> value=1 (>99)
D3=100 --> value=1 (>99)
D4=50 --> value=0 (<99)
D5=20 --> value=0 (<99)

Sheet 1:
C4=2

I've been trying to figure this out using IF THEN statements and I can't seem to get it to work the way I want it

Thanks.

 

[jtakw]

Hi,

If I understand correctly, you don't need IF THEN, just COUNTIF would do:

Excel 2010
	D
2	100
3	100
4	50
5	20
Sheet2

Excel 2010
	C
4	2
Sheet1

Cell Formulas
Range	Formula
C4	=COUNTIF(Sheet2!D2:D5,">99")

 

[mleunga]

Thanks so much- this works

 

[jtakw]

You're welcome, welcome to the forum.