Counting question

[Zee_fun]

Can't seem to figure out the formula. I need to count column "I" and see how many of them are >=81 and <=110. Then the formula will be copied down to count >=111 and <=149. and so on. Can you help?

 

[Aladin Akyurek]

Book3
	A	B	C	D	E
1
2	123			81	5
3	125			111	6
4	149			150
5	135
6	164
7	85
8	164
9	75
10	108
11	90
12	74
13	125
14	87
15	165
16	191
17	73
18	189
19	118
20	85
Sheet1

The formula in E2, which is copied down up to but excluding the row corresponding to the last value in D, is:

=SUMPRODUCT(--($A$2:$A$20>=D2),--($A$2:$A$20<D3))

 

[gaftalik]

Dear Aladin,

Would you write please the sumif and the sum(if variations

Thank you Champ!

 

[Aladin Akyurek]

...
Would you write please the sumif and the sum(if variations...

1]

=SUMPRODUCT(--($A$2:$A$20>=D2),--($A$2:$A$20 < D3))

2]

=COUNTIF($A$2:$A$20,">="&D2)-COUNTIF($A$2:$A$20,">"&D3)

3]

=SUM(($A$2:$A$20>=D2)*($A$2:$A$20 < D3))

4]

=SUM(IF($A$2:$A$20>=D2,IF($A$2:$A$20 < D3,1,0),0))

5]

=COUNT(IF($A$2:$A$20>=D2,IF($A$2:$A$20 < D3,1)))

[3] to [5] requires confirming with control+shift+enter.

And no SumIf for counting.

 

[gaftalik]

What about dcount champ ?

 

[Yogi Anand]

What about dcount champ ?

Here we go ...

Book2
	A	B	C	D	E
1	Field1
2	123	81	FALSE		TRUE
3	125	111
4	149	149	5		6
5	135
6	164
7	85
8	164
9	75
10	108
11	90
12	74
13	125
14	87
15	165
16	191
17	73
18	189
19	118
20	85
Sheet2

 

[gaftalik]

As expected, thank you

 

[Aladin Akyurek]

What about dcount champ ?

Champ?

Here a method of mine that makes a DCOUNT formula is copiable for many rows of criteria without repeating the header row...

Book3
	A	B	C	D	E	F
1	X			X	X	DCount
2	123		81	>=81	<111	5
3	125		111	>=111	<150	6
4	149		150
5	135
6	164
7	85
8	164
9	75
10	108
11	90
12	74
13	125
14	87
15	165
16	191
17	73
18	189
19	118
20	85
Sheet1

D2, copied down as far as needed:

=">="&C2

E2, copied down as far as needed:

="<"&C3

F2, copied down as far as needed:

=DCOUNT($A$1:$A$20,1,$D$1:E2)-SUM($F$1:F1)

The data table method, revealed by MS, is another that allows for not repeating the header row of the criteria range.

For the latter and more see my contrib in:

http://216.92.17.166/board2/viewtopic.php?t=60895

 

[gaftalik]

That's a knock out !No comment !
Champ your 3rd formula shall be entered as Ctrl+shift +enter isnt it ?
{=SUM(($A$2:$A$20>=D2)*($A$2:$A$20 < D3))}

 

[gaftalik]

My God! that's incredible !