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*"}))
| A | B | C | D | F |
| Status | Original Expected Project Cost | Expected Total Project Cost | I 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)
| A | B | C | D | E |
| Status | Original Expected Project Cost | Expected Total Project Cost | I 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
| A | B | C | D | E | F | G | H | I |
| 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)
(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%
| A | B | C | D | E | F | G | H | I |
| 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)
(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) |
