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

raul8

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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)
BCDE
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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
@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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,655
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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web

ADVERTISEMENT

@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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,655
Consider:

Book2
ABCDEFGHI
1StatusOriginal Execution Start DateExecution Start dateOriginal Execution Complete DateExecution Complete DateI 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
2Hold4/19/2020 20:004/19/2020 20:003/18/2021 20:003/31/2022 0:00711333114%\Management\BP
3Hold4/19/2020 20:004/19/2020 20:003/18/2021 20:009/29/2022 20:00893333168%\Management\BP
4Hold8/2/2021 20:008/2/2021 20:002/17/2022 20:002/17/2022 20:001991990%\Management\BP
5P5-12/28/2023 19:0012/28/2023 19:00   \Management\BP
6P5-3/1/2021 0:002/28/2021 19:003/31/2022 0:003/31/2022 0:003963950%\Management\BP
7P6-5/2/2021 20:005/2/2021 20:006/30/2021 20:008/30/2021 20:0012059103%\Management\BP
8P6-5/2/2021 20:005/16/2021 20:008/27/2021 20:009/29/2021 20:0013611716%\Management\BP
9
102455143671%
111019
12169.8333333
13
1471%
151019
16169.8333333
Data
Cell Formulas
RangeFormula
F2:F8F2=IF(AND(C2>0,E2>0),DAYS(E2,C2),"")
G2:G8G2=IF(AND(B2>0,D2>0),DAYS(D2,B2),"")
H2:H8H2=IFERROR(F2/G2-1,"")
F10:G10F10=SUM(SUMIFS(F2:F$8,$A$2:$A$8,{"Hold*","P5*","P6*"},$I$2:$I$8,"*BP"))
H10H10=F10/G10-1
H11H11=F10-G10
H12H12=(F10-G10)/SUM(COUNTIFS($A$2:$A$8,{"Hold*","P5*","P6*"},$I$2:$I$8,"*BP",$F$2:$F$8,">0"))
D14D14=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
D15D15=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))
D16D16=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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web

ADVERTISEMENT

@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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
@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))))
@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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,655
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

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
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)
abcdefghijklmnopqrstuvwxyzaaab
77864​
areaHoldtexttext2 - Hightext
8/18/2020 8:00 PM​
8/18/2020 8:00 PM​
4/29/2021 8:00 PM​
150000​
228000​
YellowGreentext
7/30/2020 8:00 PM​
8/4/2020 8:00 PM​
8/3/2020 8:00 PM​
texttext
9/9/2021 8:43 AM​
9/9/21 - text
5000001​
EpicNew\Management\BP
71321​
areaP5- texttexttext3 - Mediumtext
8/30/2021 8:00 PM​
7/18/2021 8:00 PM​
7/18/2021 8:00 PM​
11/12/2021 8:00 PM​
12/17/2021 8:00 PM​
58000​
58000​
GreenGreentexttexttext
8/30/2021 5:20 PM​
9/9/21 - text
540000​
EpicActive\Management\BP
106537​
areaP5- texttexttext2 - Hightext
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​
GreenGreentext
4/30/2021 8:00 PM​
7/4/2021 8:00 PM​
6/29/2021 8:00 PM​
texttext
9/13/2021 7:35 AM​
9/9/21 - text
30000​
EpicActive\Management\BP
99730​
areaP5- texttexttext1 - Criticaltext
3/13/2022 8:00 PM​
3/31/2021 8:00 PM​
3/31/2021 8:00 PM​
3/30/2022 8:00 PM​
3/30/2022 8:00 PM​
800000​
800000​
GreenGreentext
3/28/2021 8:00 PM​
3/28/2021 8:00 PM​
texttext
6/18/2021 3:50 PM​
9/9/21 - text
8000000​
EpicActive\Management\BP
78140​
areaP5- texttexttext1 - Criticaltext
2/28/2022 4:00 AM​
12/10/2020 7:00 PM​
12/10/2020 7:00 PM​
10/14/2021 8:00 PM​
2/27/2022 7:00 PM​
159000​
250000​
GreenGreentext
5/4/2020 8:00 PM​
11/2/2020 7:00 PM​
6/18/2020 8:00 PM​
texttext
8/3/2021 2:14 PM​
9/9/21 - text
510000​
EpicActive\Management\BP
100849​
areaP5- texttexttext3 - Mediumtext
12/30/2021 7:00 PM​
8/31/2021 8:00 PM​
8/31/2021 8:00 PM​
11/29/2021 7:00 PM​
11/29/2021 7:00 PM​
66000​
66000​
GreenGreentexttexttext
8/20/2021 10:22 AM​
9/9/21 - text
35000​
EpicActive\Management\BP
100802​
areaP5- texttexttext2 - Hightext
3/21/2022 7:00 PM​
9/20/2021 8:00 PM​
9/20/2021 8:00 PM​
4/4/2022 7:00 PM​
4/4/2022 7:00 PM​
218000​
218000​
GreenGreentext
6/9/2021 12:00 AM​
9/20/2021 12:00 AM​
7/6/2021 8:00 PM​
texttext
9/22/2021 8:33 AM​
9/9/21 - textEpicActive\Management\BP
106791​
areaP5- texttexttext2 - Hightext
11/29/2021 7:00 PM​
8/19/2021 8:00 PM​
8/19/2021 8:00 PM​
12/9/2021 7:00 PM​
12/9/2021 7:00 PM​
950000​
950000​
GreenGreentext
4/21/2021 12:00 AM​
8/23/2021 12:00 AM​
4/21/2021 8:00 PM​
texttext
9/8/2021 9:45 AM​
9/9/21 - text
3750000​
EpicActive\Management\BP
92436​
areaP5- texttexttext2 - Hightext
9/30/2021 8:00 PM​
3/14/2021 8:00 PM​
3/14/2021 8:00 PM​
8/30/2021 8:00 PM​
10/15/2021 8:00 PM​
165000​
180000​
GreenGreentext
1/26/2021 7:00 PM​
3/10/2021 7:00 PM​
2/8/2021 7:00 PM​
texttext
8/3/2021 6:04 PM​
9/9/21 - text
2000000​
EpicActive\Management\BP
94233​
areaP5- texttexttext3 - Mediumtext
1/31/2022 7:00 PM​
3/7/2021 7:00 PM​
3/7/2021 7:00 PM​
3/17/2022 8:00 PM​
8/25/2022 8:00 PM​
125000​
125000​
GreenGreentext
12/17/2020 7:00 PM​
2/7/2021 7:00 PM​
12/30/2021 7:00 PM​
texttext
8/24/2021 3:52 PM​
9/9/21 - text
1000000​
EpicActive\Management\BP
55611​
areaP6- texttexttext3 - Mediumtext
3/25/2022 1:00 AM​
2/25/2021 7:00 PM​
2/25/2021 7:00 PM​
3/24/2022 8:00 PM​
3/24/2022 8:00 PM​
72000​
72000​
GreenGreentext
3/3/2020 7:00 PM​
1/18/2021 7:00 PM​
4/23/2020 8:00 PM​
texttext
2/8/2021 11:55 AM​
9/9/21 - text
770000​
EpicActive\Management\BP
78728​
areaP6- texttexttext2 - Hightext
8/31/2021 8:00 PM​
1/5/2021 7:00 PM​
1/5/2021 7:00 PM​
7/31/2021 8:00 PM​
11/28/2021 7:00 PM​
130000​
130000​
RedYellowtext
7/29/2020 8:00 PM​
10/7/2020 8:00 PM​
10/1/2020 8:00 PM​
texttext
9/13/2021 7:32 AM​
9/9/21 - text
500000​
EpicActive\Management\BP
 

Forum statistics

Threads
1,147,451
Messages
5,741,200
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top