# Difficult Count if Question

#### Jose Miguel

##### Board Regular
Hello All,

I have a rather hard question to pose. At every point where there is a value in column D, I want to be able to do a countif of the values in column F, with the range being the corresponding cell in F where the value is on column D to the point above the next value in column D in F. In other words, if there is a value in D1 and the next populated value is in D20, the countif should count F1 to F19. I hope this makes sense!

Thanks

Jose

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### hatman

##### Well-known Member
How's this:
Book1
DEFGH
1 tom
2 ****
3 harry
41 polly
5 kelley
6 john
7 jane
8 scott
9 bubba
10 bueford
11 jethro
12 tom
1321****
14 harry
15 polly
16 kelley
17 john
18 tom
19 scott
20 tom
21 bueford
22 jethro
23 tom
24 ****
25103harry
26 polly
27 kelley
Sheet3

E2 is =IF(AND(D2<>"",COUNTA(\$D\$1:D1)>0),SUM(IF(ROW(\$D\$1:D1)>MAX(IF(\$D\$1:D1<>"",ROW(\$D\$1:D1),0)),IF(\$F\$1:F1=\$H\$1,1,0),0)),"") confirmed with Ctrl+Shift+Enter and copied down. Will count the number of times that the value in H1 appears in column F...

#### barry houdini

##### MrExcel MVP
Hello All,

I have a rather hard question to pose. At every point where there is a value in column D, I want to be able to do a countif of the values in column F, with the range being the corresponding cell in F where the value is on column D to the point above the next value in column D in F. In other words, if there is a value in D1 and the next populated value is in D20, the countif should count F1 to F19. I hope this makes sense!

Thanks

Jose

What sort of values do you have in column D, numbers or text? What do you want to count in column F?

Replies
0
Views
49
Replies
8
Views
95
Replies
1
Views
75
Replies
4
Views
137
Replies
4
Views
68

1,109,367
Messages
5,528,271
Members
409,813
Latest member
robyrux

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...