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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,825
Welcome to the MrExcel forum!

I'm a little hazy on your requirements, but try this (adjusting the ranges as necessary):

Excel Formula:
=SUMPRODUCT(--ISNUMBER(MATCH(B2:B200,{"Hold","P1","P4"},0)),--(C2:C200<D2:D200),--ISNUMBER(SEARCH("BP",E2:E200)))
 

raul8

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
Thank Eric. Please tell me what questions do you have about my requirement.

I tried your suggested formula with minor changes but i got an error. This is what i tired to use (I added the Data! to call the tab where the source is and added * to change from absolute to contain)
=SUMPRODUCT(--ISNUMBER(MATCH(Data!C2:C200,{"*Hold*","*P5*","*P6*"},0)),--(Data!K2:200<Data!L2:L200),--ISNUMBER(SEARCH("*BP*",Data!AB2:AB200)))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,825
Unfortunately, using * as a wildcard is not consistent in Excel formulas. You'd need to do something like this:

Excel Formula:
=SUMPRODUCT(SIGN(MMULT(--ISNUMBER(SEARCH({"Hold","P1","P4"},Data!C2:C200)),{1;1;1})),--(Data!K2:K200<Data!L2:L200),--ISNUMBER(SEARCH("BP",Data!AB2:AB200)))

Or if you have Excel 365:

Excel Formula:
=ROWS(FILTER(Data!C2:C200,MMULT(--ISNUMBER(SEARCH({"Hold","P1","P4"},Data!C2:C200)),{1;1;1})*(Data!K2:K200<Data!L2:L200)*ISNUMBER(SEARCH("BP",Data!AB2:AB200))))

The 365 version might be preferred, since if you want to SUM or AVERAGE some data, just change the ROWS to SUM or AVERAGE, and change the first range in the formula to the range with the values you want to SUM or AVERAGE.

If you're not comfortable with the MMULT in these formulas, let me know and I'll rewrite them without it. They'll be longer, but maybe easier to understand.
 

raul8

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

ADVERTISEMENT

Unfortunately, using * as a wildcard is not consistent in Excel formulas. You'd need to do something like this:

Excel Formula:
=SUMPRODUCT(SIGN(MMULT(--ISNUMBER(SEARCH({"Hold","P1","P4"},Data!C2:C200)),{1;1;1})),--(Data!K2:K200<Data!L2:L200),--ISNUMBER(SEARCH("BP",Data!AB2:AB200)))

Or if you have Excel 365:

Excel Formula:
=ROWS(FILTER(Data!C2:C200,MMULT(--ISNUMBER(SEARCH({"Hold","P1","P4"},Data!C2:C200)),{1;1;1})*(Data!K2:K200<Data!L2:L200)*ISNUMBER(SEARCH("BP",Data!AB2:AB200))))

The 365 version might be preferred, since if you want to SUM or AVERAGE some data, just change the ROWS to SUM or AVERAGE, and change the first range in the formula to the range with the values you want to SUM or AVERAGE.

If you're not comfortable with the MMULT in these formulas, let me know and I'll rewrite them without it. They'll be longer, but maybe easier to understand.
Those two formulas worked like a charm! a life saver! and no way I would have been able to write such formula! I am soo very grateful!. Thank you

But i forgot 1 parameter to ignore a row if there is no data (a blank) in column K, L, M or N

With those as a 'base' I was able to unlock similar calculations for other matrices i am trying to build. and with that i yet hit another wall to find the average when trying to SUM the difference btwn columns and divide them (containing $numbers) and more intricate doing the same but counting the number of days lapsed. Let me explain:

1) Get the Average spend Variance. Formula to calculate (Total Spend / Original Planned Spend) with the same filters as before (items on "Hold,P1,P2) (from "BP"). I tried:
a) To get a $ figure difference (total all column M) - (total all column N)
=SUMPRODUCT(SUMIFS(DataF:F,Data!A:A,{"*Hold*";"*P5*";"*P6*"},Data!i:i,{"*BP*"}))-(SUMIFS(Data!G:G,Data!C:C,{"*Hold*";"*P5*";"*P6*"},Data!AB:AB,{"*BP*"}))

ABCDF
StatusOriginal Expected Project CostExpected Total Project CostI added this column manually for calculations
Formula is (C - B)
Area Path
Hold
113000​
113000​
\Management\BP
Hold
50000​
50000​
\Management\BP
Hold
58000​
58000​
0​
\Management\BP
P05-
55000​
49000​
-6000​
\Management\BP
P05-
800000​
800000​
0​
\Management\BP
P6-
659858​
360450​
-299408​
\Management\BP
P6-
100000​
100000​
\Management\BP
Average of D:D --->
-6058.29​

b) on a separate cell to get the % of said variance inwhere M is the 'original expected cost and N is the actual cost, same as above but / (divide) instead of -(minus)
ABCDE
StatusOriginal Expected Project CostExpected Total Project CostI added this column manually for calculations
Formula is (C/B)-1
Area Path
Hold
113000​
#DIV/0!​
\Management\BP
Hold
50000​
#DIV/0!​
\Management\BP
Hold
58000​
58000​
0%​
\Management\BP
P05-
55000​
49000​
-11%​
\Management\BP
P05-
800000​
800000​
0%​
\Management\BP
P6-
659858​
360450​
-45%​
\Management\BP
P6-
100000​
#DIV/0!​
\Management\BP
#DIV/0!​
<--Average of E:E in %

2) Is the same as above but dealing with number of Days instead of $. where in the formula you gave me B,C,D,E are actual dates
ABCDEFGHI
StatusOriginal Execution Start DateExecution Start dateOriginal Execution Complete DateExecution Complete DateI added this column manually for calculations (E - C) (Actual)
(formla is =DAYS(E3,C3))
I added this column manually for calculations (D-B) (Original)
(formla is =DAYS(D3,B3))
I added this column manually for calculations (F/G) (Average for the row)
(Formula is =(F3/G3)-1)
Area Path
Hold
4/19/2020 20:00​
4/19/2020 20:00​
3/18/2021 20:00​
3/31/2022 0:00​
711​
333​
114%​
\Management\BP
Hold
4/19/2020 20:00​
4/19/2020 20:00​
3/18/2021 20:00​
9/29/2022 20:00​
893​
333​
168%​
\Management\BP
Hold
8/2/2021 20:00​
8/2/2021 20:00​
2/17/2022 20:00​
2/17/2022 20:00​
199​
199​
0%​
\Management\BP
P05-
12/28/2023 19:00​
12/28/2023 19:00​
45288​
45288​
0%​
\Management\BP
P05-
3/1/2021 0:00​
2/28/2021 19:00​
3/31/2022 0:00​
3/31/2022 0:00​
396​
395​
0%​
\Management\BP
P6-
5/2/2021 20:00​
5/2/2021 20:00​
6/30/2021 20:00​
8/30/2021 20:00​
120​
59​
103%​
\Management\BP
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
57%<-- formula used =AVERAGE(H3:H9)

B)Similar to above but actual numbers, not%
ABCDEFGHI
StatusOriginal Execution Start DateExecution Start dateOriginal Execution Complete DateExecution Complete DateI added this column manually for calculations (E - C) (Actual)
(formla is =DAYS(E3,C3))
I added this column manually for calculations (D-B) (Original)
(formla is =DAYS(D3,B3))
I added this column manually for calculations (F-G) (Average for the row)
(Formula is =F3-G3)
Area Path
Hold
4/19/2020 20:00​
4/19/2020 20:00​
3/18/2021 20:00​
3/31/2022 0:00​
711​
333​
378​
\Management\BP
Hold
4/19/2020 20:00​
4/19/2020 20:00​
3/18/2021 20:00​
9/29/2022 20:00​
893​
333​
560​
\Management\BP
Hold
8/2/2021 20:00​
8/2/2021 20:00​
2/17/2022 20:00​
2/17/2022 20:00​
199​
199​
0​
\Management\BP
P05-
12/28/2023 19:00​
12/28/2023 19:00​
45288​
45288​
0​
\Management\BP
P05-
3/1/2021 0:00​
2/28/2021 19:00​
3/31/2022 0:00​
3/31/2022 0:00​
396​
395​
1​
\Management\BP
P6-
5/2/2021 20:00​
5/2/2021 20:00​
6/30/2021 20:00​
8/30/2021 20:00​
120​
59​
61​
\Management\BP
P6-
5/2/2021 20:00​
5/16/2021 20:00​
8/27/2021 20:00​
9/29/2021 20:00​
136​
117​
19​
\Management\BP
145.57<-- formula used =AVERAGE(H3:H9)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,825
Consider this:

Book1 (version 1).xlsb
ABCDE
1StatusOriginal Expected Project CostExpected Total Project CostI added this column manually for calculationsArea Path
2Hold113000113000\Management\BP
3Hold5000050000\Management\BP
4Hold58000580000\Management\BP
5P5-5500049000-6000\Management\BP
6P5-8000008000000\Management\BP
7P6-659858360450-299408\Management\BP
8P6-100000100000\Management\BP
9
101a-6058.29no d column
111a-6058.29with d column
121a-6058.29Excel 365, no d column
13
141b-2.70%No other variations that make sense
Data
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B10B10=(SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))-SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*")))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))
B11B11=SUM(SUMIFS(Data!D2:D8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))
B12B12=AVERAGE(FILTER(Data!C2:C8-Data!B2:B8,MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!E2:E8))))
B14B14=SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))/SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))-1


As far as your question 2a, use the B14 formula and use the columns with the start and end dates. For your 2b question, you can use the B10 or B11 formula, by changing the columns to look at the start and end dates, then removing everything after and including the / sign. Or use the B12 formula, changing AVERAGE to SUM.

But i forgot 1 parameter to ignore a row if there is no data (a blank) in column K, L, M or N
Is this something that you want added to these formulas, or the formulas in post 4?


Also, FYI, there is a tool called XL2BB which makes it much easier to read your sample data. Check the link in the reply box, or my signature. It's easy to download, install, and use. It's what I just used.
 

raul8

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

ADVERTISEMENT

Consider this:

Book1 (version 1).xlsb
ABCDE
1StatusOriginal Expected Project CostExpected Total Project CostI added this column manually for calculationsArea Path
2Hold113000113000\Management\BP
3Hold5000050000\Management\BP
4Hold58000580000\Management\BP
5P5-5500049000-6000\Management\BP
6P5-8000008000000\Management\BP
7P6-659858360450-299408\Management\BP
8P6-100000100000\Management\BP
9
101a-6058.29no d column
111a-6058.29with d column
121a-6058.29Excel 365, no d column
13
141b-2.70%No other variations that make sense
Data
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B10B10=(SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))-SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*")))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))
B11B11=SUM(SUMIFS(Data!D2:D8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))
B12B12=AVERAGE(FILTER(Data!C2:C8-Data!B2:B8,MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!E2:E8))))
B14B14=SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))/SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*"))-1


As far as your question 2a, use the B14 formula and use the columns with the start and end dates. For your 2b question, you can use the B10 or B11 formula, by changing the columns to look at the start and end dates, then removing everything after and including the / sign. Or use the B12 formula, changing AVERAGE to SUM.


Is this something that you want added to these formulas, or the formulas in post 4?


Also, FYI, there is a tool called XL2BB which makes it much easier to read your sample data. Check the link in the reply box, or my signature. It's easy to download, install, and use. It's what I just used.
let me plug them in and see. thank you!

About the ignoring blank cells, yes all formulas should ignore calculation of rows if any of the columns for that row are blank. I see that if any of the two columns is blank for any given row, it throws off the calculation results, specially the averages. SO for the sheet you shared, the return of the calculations should be for rows 4,5,6,7 only and row 1,2,8 ignored because they have blank cells on column B (same should be if column C was blank). I am working separately to highlight or bring visual somehow to/of any cells needed for calculations that are blank

About the XL2BB tool, I am using a company issued computer so I am not allowed to install things at will on it.
 

raul8

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
@Eric W should I use *""* somewhere in the formula to ignore the blanks? well i tried it but no luck, will it need to be written differently?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,825
Here's how to add a condition that column B must be >0:

Book1
ABCDE
1StatusOriginal Expected Project CostExpected Total Project CostI added this column manually for calculationsArea Path
2Hold113000113000\Management\BP
3Hold5000050000\Management\BP
4Hold58000580000\Management\BP
5P5-5500049000-6000\Management\BP
6P5-8000008000000\Management\BP
7P6-659858360450-299408\Management\BP
8P6-100000100000\Management\BP
9
101a-76352.00no d column
111a-76352.00with d column
121a-76352.00Excel 365, no d column
13
141b-19.42%No other variations that make sense
Data
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B10B10=(SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))-SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0")))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))
B11B11=SUM(SUMIFS(Data!D2:D8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))
B12B12=AVERAGE(FILTER(Data!C2:C8-Data!B2:B8,MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!E2:E8))*(Data!B2:B8>0)))
B14B14=SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))/SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))-1


You can see it added in all the SUMIFS functions. I also added it to the FILTER function if you have Excel 365. Adding another condition for column C would be done in the exact same way.
 

raul8

New Member
Joined
Sep 21, 2021
Messages
35
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Web
Here's how to add a condition that column B must be >0:

Book1
ABCDE
1StatusOriginal Expected Project CostExpected Total Project CostI added this column manually for calculationsArea Path
2Hold113000113000\Management\BP
3Hold5000050000\Management\BP
4Hold58000580000\Management\BP
5P5-5500049000-6000\Management\BP
6P5-8000008000000\Management\BP
7P6-659858360450-299408\Management\BP
8P6-100000100000\Management\BP
9
101a-76352.00no d column
111a-76352.00with d column
121a-76352.00Excel 365, no d column
13
141b-19.42%No other variations that make sense
Data
Cell Formulas
RangeFormula
D2:D8D2=C2-B2
B10B10=(SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))-SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0")))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))
B11B11=SUM(SUMIFS(Data!D2:D8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))/SUM(COUNTIFS(Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))
B12B12=AVERAGE(FILTER(Data!C2:C8-Data!B2:B8,MMULT(--ISNUMBER(SEARCH({"Hold","P5","P6"},Data!A2:A8)),{1;1;1})*ISNUMBER(SEARCH("BP",Data!E2:E8))*(Data!B2:B8>0)))
B14B14=SUM(SUMIFS(Data!C2:C8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))/SUM(SUMIFS(Data!B2:B8,Data!A2:A8,{"*Hold*","*P5*","*P6*"},Data!E2:E8,"*BP*",Data!B2:B8,">0"))-1


You can see it added in all the SUMIFS functions. I also added it to the FILTER function if you have Excel 365. Adding another condition for column C would be done in the exact same way.
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,987
Members
425,320
Latest member
Galin

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