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

#### raul8

##### New Member
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

@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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

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)

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

@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!

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!

@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 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))))

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?

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 text 2 - High text 8/18/2020 8:00 PM​ 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 text 9/9/2021 8:43 AM​ 9/9/21 - text 5000001​ Epic New \Management\BP 71321​ area P5- text text text 3 - Medium text 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​ Green Green text text text 8/30/2021 5:20 PM​ 9/9/21 - text 540000​ Epic Active \Management\BP 106537​ area P5- text 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 Green text 4/30/2021 8:00 PM​ 7/4/2021 8:00 PM​ 6/29/2021 8:00 PM​ text text 9/13/2021 7:35 AM​ 9/9/21 - text 30000​ Epic Active \Management\BP 99730​ area P5- text text text 1 - Critical text 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​ Green Green text 3/28/2021 8:00 PM​ 3/28/2021 8:00 PM​ text text 6/18/2021 3:50 PM​ 9/9/21 - text 8000000​ Epic Active \Management\BP 78140​ area P5- text text text 1 - Critical text 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​ Green Green text 5/4/2020 8:00 PM​ 11/2/2020 7:00 PM​ 6/18/2020 8:00 PM​ text text 8/3/2021 2:14 PM​ 9/9/21 - text 510000​ Epic Active \Management\BP 100849​ area P5- text text text 3 - Medium text 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​ Green Green text text text 8/20/2021 10:22 AM​ 9/9/21 - text 35000​ Epic Active \Management\BP 100802​ area P5- text text text 2 - High text 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​ Green Green text 6/9/2021 12:00 AM​ 9/20/2021 12:00 AM​ 7/6/2021 8:00 PM​ text text 9/22/2021 8:33 AM​ 9/9/21 - text Epic Active \Management\BP 106791​ area P5- text text text 2 - High text 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​ Green Green text 4/21/2021 12:00 AM​ 8/23/2021 12:00 AM​ 4/21/2021 8:00 PM​ text text 9/8/2021 9:45 AM​ 9/9/21 - text 3750000​ Epic Active \Management\BP 92436​ area P5- text text text 2 - High text 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​ Green Green text 1/26/2021 7:00 PM​ 3/10/2021 7:00 PM​ 2/8/2021 7:00 PM​ text text 8/3/2021 6:04 PM​ 9/9/21 - text 2000000​ Epic Active \Management\BP 94233​ area P5- text text text 3 - Medium text 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​ Green Green text 12/17/2020 7:00 PM​ 2/7/2021 7:00 PM​ 12/30/2021 7:00 PM​ text text 8/24/2021 3:52 PM​ 9/9/21 - text 1000000​ Epic Active \Management\BP 55611​ area P6- text text text 3 - Medium text 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​ Green Green text 3/3/2020 7:00 PM​ 1/18/2021 7:00 PM​ 4/23/2020 8:00 PM​ text text 2/8/2021 11:55 AM​ 9/9/21 - text 770000​ Epic Active \Management\BP 78728​ area P6- text text text 2 - High text 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​ Red Yellow text 7/29/2020 8:00 PM​ 10/7/2020 8:00 PM​ 10/1/2020 8:00 PM​ text text 9/13/2021 7:32 AM​ 9/9/21 - text 500000​ Epic Active \Management\BP

Replies
4
Views
232
Replies
4
Views
282
Replies
4
Views
240
Replies
0
Views
168
Replies
11
Views
371

1,196,413
Messages
6,015,119
Members
441,872
Latest member
Jyyyyyyyy

### 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.

### Which adblocker are you using?

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

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