Hello everyone,
I have this formula (Thank you Eric W) that is counting the same cell twice because the "if" condition is present on that same cell twice but the intend is to look at that cell and if any of the conditions exist, to count it once only. Need some help in solving that. Thank you all!: (on the sample below, I am getting a return of 4 but I expect and it should be a return of 3)
=SUMPRODUCT(COUNTIFS(Data!C:C,{"*Hold*";"*P5*";"*P6*"},Data!AB:AB,"*BP*",Data!V:V,{"*test5*","*test3*"}))
I have this formula (Thank you Eric W) that is counting the same cell twice because the "if" condition is present on that same cell twice but the intend is to look at that cell and if any of the conditions exist, to count it once only. Need some help in solving that. Thank you all!: (on the sample below, I am getting a return of 4 but I expect and it should be a return of 3)
=SUMPRODUCT(COUNTIFS(Data!C:C,{"*Hold*";"*P5*";"*P6*"},Data!AB:AB,"*BP*",Data!V:V,{"*test5*","*test3*"}))
Copy of 10_05_2021 Portfolio Validation.xlsx | ||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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 | |||
1 | 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 | ||
2 | 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 | 9/9/2021 8:43 AM | 9/9/21 - text | 5000001 | Epic | New | \Management\BP | |||||
3 | 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 | 8/30/2021 5:20 PM | 9/9/21 - text | 540000 | Epic | Active | \Management\BP | ||||||
4 | 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 | Test 1 | 9/13/2021 7:35 AM | 9/9/21 - text | 30000 | Epic | Active | \Management\BP | ||
5 | 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 | 6/18/2021 3:50 PM | 9/9/21 - text | 8000000 | Epic | Active | \Management\BP | ||||
6 | 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 | Test2;test4;test5 | 8/3/2021 2:14 PM | 9/9/21 - text | 510000 | Epic | Active | \Management\BP | ||
7 | 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 | 8/20/2021 10:22 AM | 9/9/21 - text | 35000 | Epic | Active | \Management\BP | ||||||
8 | 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 | 9/22/2021 8:33 AM | 9/9/21 - text | Epic | Active | \Management\BP | ||||
9 | 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 | test1 | 9/8/2021 9:45 AM | 9/9/21 - text | 3750000 | Epic | Active | \Management\BP | ||
10 | 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 | test4,test3 | 8/3/2021 6:04 PM | 9/9/21 - text | 2000000 | Epic | Active | \Management\BP | ||
11 | 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 | test1 | 8/24/2021 3:52 PM | 9/9/21 - text | 1000000 | Epic | Active | \Management\BP | ||
12 | 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 | test3,test5 | 2/8/2021 11:55 AM | 9/9/21 - text | 770000 | Epic | Active | \Management\BP | ||
13 | 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 | test4 | 9/13/2021 7:32 AM | 9/9/21 - text | 500000 | Epic | Active | \Management\BP | ||
Sheet4 |