Simplifying a countif formula or new way to count 2 date columns when one is > than the other across 200 rows

raul8 · Sep 21, 2021

Hello all,

Thank you in advance for helping on this. I spent a good amount of time trying to make this formula work and, as a noob, i was able to make it work using 'countifs' but quickly realized that i would need to type this formula over 200 times in the same cell to return the count i am looking for.
=COUNTIFS(Data!C25,"<"&Data!D25)+COUNTIFS(Data!C18,"<"&Data!D18)+COUNTIFS(Data!C36,"<"&Data!D36)+COUNTIFS(Data!C49,"<"&Data!D49)

So I have 200+ rows and need to count the amount of times the date on column C is lower/earlier than the date in column D and further filter the result by columns B if the row contains Hold, P1 or P4 and E if E contains BP (the formula above does not have this filtering range as I didnt even get to that point yet)

B	C	D	E
Hold	8/18/2020 20:00	7/30/2021 20:00	\Management\BP
P1	1/28/2021 20:00	10/30/2021 20:00	\Management\TK
P2			\Management\BP
P3			\Management\TK
P5			\Management\BP
P6			\Management\TK

I am also open to other suggestions on getting this and other data from the same data source to sum and average, where be by the use of VBA or PowerBI or PivotTables

raul8 · Sep 28, 2021

@Eric W , this worked well for the small sample data i have been working on. Again many thanks.

How can this formula be made to for the 'part 2' which deals with dates on 4 different columns. here it is more critical to ignore a row if any of the 4 column cells are empty/blank

Eric W · Sep 28, 2021

raul8 said:
I see the addition, yeah, i wasn't going the right direction with it. I guess I can also add Data!C2:C8,">0" to ignore empty cells in Column C

Exactly!

Rich (BB code):

How can this formula be made to for the 'part 2' which deals with dates on 4 different columns. here it is more critical to ignore a row if any of the 4 column cells are empty/blank

Same way as with Data!C2:C8,">0" would probably work. If the 4 columns are adjacent, I could do the MMULT trick, but it's clearer without it.

raul8 · Sep 28, 2021

Eric W said:
Exactly!

Rich (BB code):

How can this formula be made to for the 'part 2' which deals with dates on 4 different columns. here it is more critical to ignore a row if any of the 4 column cells are empty/blank

Same way as with Data!C2:C8,">0" would probably work. If the 4 columns are adjacent, I could do the MMULT trick, but it's clearer without it.

I am trying to give a tab at it but to me the complexity is that before we had 2 columns from the source to add/subtract and then get the return. But now with the dates one, we need to get the difference in days from 2 columns, then the difference in days from 2 other columns, then make the calculations of average # of days and average of % from those resulting 2 new numbers. all while ignoring a row if a given date (cell in that colum) is blank/missing (so i can't use the ">0" command)

raul8 · Sep 29, 2021

raul8 said:
I am trying to give a tab at it but to me the complexity is that before we had 2 columns from the source to add/subtract and then get the return. But now with the dates one, we need to get the difference in days from 2 columns, then the difference in days from 2 other columns, then make the calculations of average # of days and average of % from those resulting 2 new numbers. all while ignoring a row if a given date (cell in that colum) is blank/missing (so i can't use the ">0" command)

@Eric W i tried this formula and kinds of variations, again errors of too many arguments or formula being wrong:

this is to get average days (a #):
AVERAGE(FILTER(days(Data!k2:k89,Data!i2:i89)-(days(Data!L2:L89,data!J2:J89),MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89)*(Data!k2:k89>0)*(Data!i2:i89>0)*(Data!L2:L89>0)*(Data!J2:J89>0))))

This is to get the average in %:
SUM(SUMIFS(days(Data!k2:k89,data!i2:89),Data!C2:C89,{"*Hold*","*P5*","*P6*"},Data!AB2:AB89,"*BP*",Data!k2:k89,">0",data!i2:i89,">0"))/SUM(SUMIFS(days(Data!L2:L89,data!J2:J89),Data!C2:C89,{"*Hold*","*P5*","*P6*"},Data!AB2:AB89,"*BP*",Data!L2:L89,">0",data!J2:J89,">0"))-1

raul8 · Oct 4, 2021

raul8 said:
@Eric W i tried this formula and kinds of variations, again errors of too many arguments or formula being wrong:

this is to get average days (a #):
AVERAGE(FILTER(days(Data!k2:k89,Data!i2:i89)-(days(Data!L2:L89,data!J2:J89),MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89)*(Data!k2:k89>0)*(Data!i2:i89>0)*(Data!L2:L89>0)*(Data!J2:J89>0))))

This is to get the average in %:
SUM(SUMIFS(days(Data!k2:k89,data!i2:89),Data!C2:C89,{"*Hold*","*P5*","*P6*"},Data!AB2:AB89,"*BP*",Data!k2:k89,">0",data!i2:i89,">0"))/SUM(SUMIFS(days(Data!L2:L89,data!J2:J89),Data!C2:C89,{"*Hold*","*P5*","*P6*"},Data!AB2:AB89,"*BP*",Data!L2:L89,">0",data!J2:J89,">0"))-1

@Eric W any ideas to help solve this? much appreciated!

Eric W · Oct 4, 2021

Consider:

Book2

A

B

C

D

E

F

G

H

I

1

Status

Original Execution Start Date

Execution Start date

Original Execution Complete Date

Execution Complete Date

I added this column manually for calculations (E - C) (Actual)

I added this column manually for calculations (D-B) (Original)

I added this column manually for calculations (F/G) (Average for the row)

Area Path

2

Hold

4/19/2020 20:00

3/18/2021 20:00

3/31/2022 0:00

711

333

114%

\Management\BP

3

Hold

4/19/2020 20:00

3/18/2021 20:00

9/29/2022 20:00

893

333

168%

\Management\BP

4

Hold

8/2/2021 20:00

2/17/2022 20:00

199

0%

\Management\BP

5

P5-

12/28/2023 19:00

\Management\BP

6

P5-

3/1/2021 0:00

2/28/2021 19:00

3/31/2022 0:00

396

395

0%

\Management\BP

7

P6-

5/2/2021 20:00

6/30/2021 20:00

8/30/2021 20:00

120

59

103%

\Management\BP

8

P6-

5/2/2021 20:00

5/16/2021 20:00

8/27/2021 20:00

9/29/2021 20:00

136

117

16%

\Management\BP

9

10

11

12

13

14

71%

15

1019

16

169.8333333

Data

Cell Formulas
Range	Formula
F2:F8	F2	=IF(AND(C2>0,E2>0),DAYS(E2,C2),"")
G2:G8	G2	=IF(AND(B2>0,D2>0),DAYS(D2,B2),"")
H2:H8	H2	=IFERROR(F2/G2-1,"")
F10:G10	F10	=SUM(SUMIFS(F2:F$8,$A$2:$A$8,{"Hold","P5","P6"},$I$2:$I$8,"BP"))
H10	H10	=F10/G10-1
H11	H11	=F10-G10
H12	H12	=(F10-G10)/SUM(COUNTIFS($A$2:$A$8,{"Hold","P5","P6"},$I$2:$I$8,"BP",$F$2:$F$8,">0"))
D14	D14	=SUMPRODUCT(INT(E2:E8)-INT(C2:C8),--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})ISNUMBER(SEARCH("BP",Data!I2:I8))(MMULT(--(B2:E8>0),{1;1;1;1})=4))/SUMPRODUCT(INT(D2:D8)-INT(B2:B8),--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})ISNUMBER(SEARCH("BP",Data!I2:I8))(MMULT(--(B2:E8>0),{1;1;1;1})=4))-1
D15	D15	=SUMPRODUCT(INT(E2:E8)-INT(C2:C8)+INT(B2:B8)-INT(D2:D8),--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})ISNUMBER(SEARCH("BP",Data!I2:I8))(MMULT(--(B2:E8>0),{1;1;1;1})=4))
D16	D16	=SUMPRODUCT(INT(E2:E8)-INT(C2:C8)+INT(B2:B8)-INT(D2:D8),--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})ISNUMBER(SEARCH("BP",Data!I2:I8))(MMULT(--(B2:E8>0),{1;1;1;1})=4))/SUMPRODUCT(--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})ISNUMBER(SEARCH("BP",Data!I2:I8))(MMULT(--(B2:E8>0),{1;1;1;1})=4))

Even though you need the F:H helper columns, you might want to keep using them. The F10 and H10:H12 formulas are much easier to understand and maintain that the single cell formulas in D14:D16.

Hope this helps!

raul8 · Oct 5, 2021

@Eric W once again it works with the sample data, I will be pluggin it in with the actual prod data soon to compare.
Question, on the string to ignore a row if a cell is 'blank' i noticed that it so happened that all the pertaining cells are next to each other hence the MMULT(--(B2:E8>0) but what if there was a column in btwn not part of the equation at all? So again I am trying to recycle the formula to run a similar calculation that involves only the average of difference btwn 2 columns (instead of 4) and those 2 columns are distant to each other. Here is the sample of how I tried to modify it: (i get #value!)

=SUMPRODUCT(INT(Data!J2:J89)-INT(Data!R2:R89),--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89))*(MMULT(--(Data!J2:J89>0),{1;1;1;1})=4))/SUMPRODUCT(--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89))*(MMULT(--(Data!R2:R89>0),{1;1;1;1})=4))

I also tried

and failed)

=AVERAGE(FILTER(days(Data!j2:j89,Data!r2:r89)),MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89)*(Data!j2:j89>0)*(Data!r2:r89>0))))

raul8 · Oct 6, 2021

raul8 said:
@Eric W once again it works with the sample data, I will be pluggin it in with the actual prod data soon to compare.
Question, on the string to ignore a row if a cell is 'blank' i noticed that it so happened that all the pertaining cells are next to each other hence the MMULT(--(B2:E8>0) but what if there was a column in btwn not part of the equation at all? So again I am trying to recycle the formula to run a similar calculation that involves only the average of difference btwn 2 columns (instead of 4) and those 2 columns are distant to each other. Here is the sample of how I tried to modify it: (i get #value!)

=SUMPRODUCT(INT(Data!J2:J89)-INT(Data!R2:R89),--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89))*(MMULT(--(Data!J2:J89>0),{1;1;1;1})=4))/SUMPRODUCT(--MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89))*(MMULT(--(Data!R2:R89>0),{1;1;1;1})=4))

I also triedand failed)

=AVERAGE(FILTER(days(Data!j2:j89,Data!r2:r89)),MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89)*(Data!j2:j89>0)*(Data!r2:r89>0))))

@Eric W , i was able to make the "Average" formula work, however i am getting a calculation that is not ignoring rows with empty cells. in this case, R2:R89 has 2 empty cells and the formula is 'counting' those rows this giving the wrong average value. seems as if the "*(Data!r2:r89>0)" is not working correctly?
=AVERAGE(FILTER(Data!J2:J89-Data!R2:R89,MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!C2:C89)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!AB2:AB89)*(Data!J2:J89>0)*(Data!R2:R89>0))))

Eric W · Oct 6, 2021

I don't see anything wrong with your latest AVERAGE formula. Without seeing the sheet with data, I can't really make any suggestions. If I were to try to debug this, I'd change all the ranges in that formula so that they all just had maybe 5 rows in them, J2:J7, R2:R7 etc., making sure that the range includes one of the empty cells in the R column. Then select the formula, use the Evaluate Formula tool, and step through it, taking special note when you get to the *(Data!r2:r89>0) part.

Is there anything in the R column that isn't showing? A formula that returns a null maybe?

raul8 · Oct 8, 2021

Eric W said:
I don't see anything wrong with your latest AVERAGE formula. Without seeing the sheet with data, I can't really make any suggestions. If I were to try to debug this, I'd change all the ranges in that formula so that they all just had maybe 5 rows in them, J2:J7, R2:R7 etc., making sure that the range includes one of the empty cells in the R column. Then select the formula, use the Evaluate Formula tool, and step through it, taking special note when you get to the *(Data!r2:r89>0) part.

Is there anything in the R column that isn't showing? A formula that returns a null maybe?

@Eric W here is the data set : (with the formula i am getting a 7504.37 result but if you dont count rows 2 and 6 as it shouldnt because R cell is empty, the expect result should be 121.6)

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

p

q

r

s

t

u

v

w

x

y

z

aa

ab

77864

area

Hold

text

2 - High

text

8/18/2020 8:00 PM

4/29/2021 8:00 PM

150000

228000

Yellow

Green

text

7/30/2020 8:00 PM

8/4/2020 8:00 PM

8/3/2020 8:00 PM

text

9/9/2021 8:43 AM

9/9/21 - text

5000001

Epic

New

\Management\BP

71321

area

P5- text

text

3 - Medium

text

8/30/2021 8:00 PM

7/18/2021 8:00 PM

11/12/2021 8:00 PM

12/17/2021 8:00 PM

58000

Green

text

8/30/2021 5:20 PM

9/9/21 - text

540000

Epic

Active

\Management\BP

106537

area

P5- text

text

2 - High

text

11/30/2021 7:00 PM

8/29/2021 8:00 PM

8/22/2021 8:00 PM

11/30/2021 7:00 PM

12/1/2021 7:00 PM

55000

49000

Green

text

4/30/2021 8:00 PM

7/4/2021 8:00 PM

6/29/2021 8:00 PM

text

9/13/2021 7:35 AM

9/9/21 - text

30000

Epic

Active

\Management\BP

99730

area

P5- text

text

1 - Critical

text

3/13/2022 8:00 PM

3/31/2021 8:00 PM

3/30/2022 8:00 PM

800000

Green

text

3/28/2021 8:00 PM

text

6/18/2021 3:50 PM

9/9/21 - text

8000000

Epic

Active

\Management\BP

78140

area

P5- text

text

1 - Critical

text

2/28/2022 4:00 AM

12/10/2020 7:00 PM

10/14/2021 8:00 PM

2/27/2022 7:00 PM

159000

250000

Green

text

5/4/2020 8:00 PM

11/2/2020 7:00 PM

6/18/2020 8:00 PM

text

8/3/2021 2:14 PM

9/9/21 - text

510000

Epic

Active

\Management\BP

100849

area

P5- text

text

3 - Medium

text

12/30/2021 7:00 PM

8/31/2021 8:00 PM

11/29/2021 7:00 PM

66000

Green

text

8/20/2021 10:22 AM

9/9/21 - text

35000

Epic

Active

\Management\BP

100802

area

P5- text

text

2 - High

text

3/21/2022 7:00 PM

9/20/2021 8:00 PM

4/4/2022 7:00 PM

218000

Green

text

6/9/2021 12:00 AM

9/20/2021 12:00 AM

7/6/2021 8:00 PM

text

9/22/2021 8:33 AM

9/9/21 - text

Epic

Active

\Management\BP

106791

area

P5- text

text

2 - High

text

11/29/2021 7:00 PM

8/19/2021 8:00 PM

12/9/2021 7:00 PM

950000

Green

text

4/21/2021 12:00 AM

8/23/2021 12:00 AM

4/21/2021 8:00 PM

text

9/8/2021 9:45 AM

9/9/21 - text

3750000

Epic

Active

\Management\BP

92436

area

P5- text

text

2 - High

text

9/30/2021 8:00 PM

3/14/2021 8:00 PM

8/30/2021 8:00 PM

10/15/2021 8:00 PM

165000

180000

Green

text

1/26/2021 7:00 PM

3/10/2021 7:00 PM

2/8/2021 7:00 PM

text

8/3/2021 6:04 PM

9/9/21 - text

2000000

Epic

Active

\Management\BP

94233

area

P5- text

text

3 - Medium

text

1/31/2022 7:00 PM

3/7/2021 7:00 PM

3/17/2022 8:00 PM

8/25/2022 8:00 PM

125000

Green

text

12/17/2020 7:00 PM

2/7/2021 7:00 PM

12/30/2021 7:00 PM

text

8/24/2021 3:52 PM

9/9/21 - text

1000000

Epic

Active

\Management\BP

55611

area

P6- text

text

3 - Medium

text

3/25/2022 1:00 AM

2/25/2021 7:00 PM

3/24/2022 8:00 PM

72000

Green

text

3/3/2020 7:00 PM

1/18/2021 7:00 PM

4/23/2020 8:00 PM

text

2/8/2021 11:55 AM

9/9/21 - text

770000

Epic

Active

\Management\BP

78728

area

P6- text

text

2 - High

text

8/31/2021 8:00 PM

1/5/2021 7:00 PM

7/31/2021 8:00 PM

11/28/2021 7:00 PM

130000

Red

Yellow

text

7/29/2020 8:00 PM

10/7/2020 8:00 PM

10/1/2020 8:00 PM

text

9/13/2021 7:32 AM

9/9/21 - text

500000

Epic

Active

\Management\BP

Simplifying a countif formula or new way to count 2 date columns when one is > than the other across 200 rows

raul8

New Member

raul8

New Member

Excel Facts

Eric W

MrExcel MVP

raul8

New Member

raul8

New Member

raul8

New Member

Eric W

MrExcel MVP

raul8

New Member

raul8

New Member

Eric W

MrExcel MVP

raul8

New Member

Similar threads

Forum statistics

Share this page

Simplifying a countif formula or new way to count 2 date columns when one is > than the other across 200 rows

New Member

New Member

Excel Facts

MrExcel MVP

New Member

New Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock