Count cases which happen in a certain order or date with an additional criteria

[benni55]

I reached my Excel-Limits this week and still cannot find an answer. :-/ Help would be greatly appreciated!!!

Problem:
I want to count the cases where the program is "Amp" and there comes a program "Gain" afterwards in the same City. There are very different numbers of programs in each city but the table is already filtered for Cityname and Date within the City.

Any idea?

You probably don´t need the columns "Mehrfach city", "Abfrage City first" or "count cases per city". They are just my trys to solve this case.

Table:
table.JPG

[benni55]

1
Date City Program Program Key Mehrfach City Abfrage City first Count Cases per City Counting Cases
30.04.2014 Freiburg Investive 1 4 FALSCH 4 0
30.04.2014 Freiburg Gain 2 4 WAHR 0 0
20.03.2016 Freiburg Investive 1 4 WAHR 0 0
21.03.2016 Freiburg Amp 3 4 WAHR 0 0
10.12.2015 Chemnitz Gain 2 5 FALSCH 5 0
14.12.2015 Chemnitz Investive 1 5 WAHR 0 0
19.12.2015 Chemnitz Gain 2 5 WAHR 0 0
22.12.2015 Chemnitz Amp 3 5 WAHR 0 1
10.12.2016 Chemnitz Gain 2 5 WAHR 0 0
27.01.2012 Troisdorf Investive 1 2 FALSCH 2 0
07.03.2013 Troisdorf Investive 1 2 WAHR 0 0

[Phuocam]

Try this ...

=SUMPRODUCT(($B$2:$B$11=$B$3:$B$12)*($C$2:$C$11="Amp")*($C$3:$C$12="Gain"))

[Glenn Kennedy]

Hi there. You can use SUMPRODUCT

=SUMPRODUCT(($C$2:$C$12="Amp")*($C$3:$C$13="Gain")*($B$2:$B$12=$B$3:$B$13))

[Glenn Kennedy]

Duhhh. I forgot to refresh before posting.

[benni55]

Thanks a lot! It almost fits my requirements but the count doesn´t "consider" if there is another case within the two cases.

for example like that: (I inserted a "Investive" within "Amp" and "Gain" in row 9.

Date City Program Program 0
30.04.2014 Freiburg Investive 1 4 FALSCH 4 0
30.04.2014 Freiburg Gain 2 4 WAHR 0 0
20.03.2016 Freiburg Investive 1 4 WAHR 0 0
21.03.2016 Freiburg Amp 3 4 WAHR 0 0
10.12.2015 Chemnitz Gain 2 5 FALSCH 5 0
14.12.2015 Chemnitz Investive 1 5 WAHR 0 0
19.12.2015 Chemnitz Amp 2 5 WAHR 0 0
22.12.2015 Chemnitz Investive 3 5 WAHR 0 1
10.12.2016 Chemnitz Gain 2 5 WAHR 0 0
27.01.2012 Troisdorf Investive 1 2 FALSCH 2 0
07.03.2013 Troisdorf Investive 1 2 WAHR 0 0

[benni55]

The original file is over 10.000 rows and I need to check the result at least partly by hand. Therefore I would like to have some kind of formula which finds the cases who fit the filter/formula.

For example, in a more easy formula I used a IF-Function and produced the result (just a one) on the fitting row which I summed up in the end. This way I could look some resulting "ones" up and confirm that the filter is working correctly. :-)

[Glenn Kennedy]

Not very pretty, but it works. In I2:
=SUMPRODUCT(($C$2:$C2="Amp")*($C$3:$C3="Gain")*($B$2:$B2=$B$3:$B3))

In I3, copied down:
=IF(SUMPRODUCT(($C$2:$C3="Amp")*($C$3:$C4="Gain")*($B$2:$B3=$B$3:$B4))=SUMPRODUCT(($C$2:$C2="Amp")*($C$3:$C3="Gain")*($B$2:$B2=$B$3:$B3)),0,"Here")

[Glenn Kennedy]

Change of plan... in I2:

=IF(SUMPRODUCT(($C$2:$C2="Amp")*($C$3:$C3="Gain")*($B$2:$B2=$B$3:$B3))=1,"Here",0)

In I3 and below, as previously.

[benni55]

; was wrong --> =IF(SUMPRODUCT(($C$2:$C2="Amp")*($C$3:$C3="Gain")*($B$2:$B2=$B$3:$B3))=1;"Here";0)

but the result is a lot of "here" where they shouldnt be ;-) thx!

[benni55]

Okay. I guess you forgot the $ to get in flexible.
--> =IF(SUMPRODUCT(($C2:$C2="Amp")*($C3:$C3="Gain")*($B2:$B2=$B3:$B3))=1;"Here";0)

So far it looks good. I'll be back and show my thank if it is perfect. ;-) cheers from Freiburg/Germany

[Glenn Kennedy]

No. Please use the formula in the attachment. Your one in 11 is oncorrect.

[Phuocam]

Or try this ...

=IF(AND(B2=B1;C2="Gain";C1="Amp");"Here";0)

Copy down.

[Glenn Kennedy]

Or try this ...

=IF(AND(B2=B1;C2="Gain";C1="Amp");"Here";0)

Copy down.

Much better!!!!

[benni55]

Thanks guys. But unfortunately does the IF-Formula only consider the next row. That is the real Problem. I need the formula to check all following rows from the same city. :-/

Some problem with the other Formula (@Glenn Kennedy). If you replace C8 with "Amp" and put f.e. "Investive" in C9 it doesn´t find that there is a "Gain" following behind a "Amp" because there´s something in between them.

[benni55]

30.04.2014 Freiburg Amp 1 4 FALSCH 4 0 Here
30.04.2014 Freiburg Gain 2 4 WAHR 0 0 0
20.03.2016 Freiburg Investive 1 4 WAHR 0 0 0
21.03.2016 Freiburg Amp 3 4 WAHR 0 0 0
10.12.2015 Chemnitz Gain 2 5 FALSCH 5 0 0
14.12.2015 Chemnitz Investive 1 5 WAHR 0 0 0
19.12.2015 Chemnitz Amp 2 5 WAHR 0 0 0
22.12.2015 Chemnitz Investive 3 5 WAHR 0 1 0
10.12.2016 Chemnitz Gain 2 5 WAHR 0 0 0
27.01.2012 Troisdorf Investive 1 2 FALSCH 2 0 0
07.03.2013 Troisdorf Investive 1 2 WAHR 0 0 0
0

[Glenn Kennedy]

Post a longer example showing exactly what you want. I think that you have changed your requirement???

[Phuocam]

=IFERROR(IF(AND(C2="Gain";LOOKUP(2;1/($C$1:C1="Amp");$B$1:B1)=B2);"Here";0);0)

[benni55]

You are probably right. I said "I want to count the cases where the program is "Amp" and there comes a program "Gain" afterwards in the same City."
and with "afterwards" I meant somewhere in the list of City-Cases. So a "Gain" can follow at the last one of the city-cases or directly afterwards with nothing in between. If there is just a "Amp" without a "Gain" afterwards it shouldn´t "trigger".
I can´t upload here for any reason but I post like this:

Date City Program Counting Cases
30.04.2014 Freiburg Amp 1
30.04.2014 Freiburg Gain
20.03.2016 Freiburg Investive
21.03.2016 Freiburg Amp
10.12.2015 Chemnitz Gain
14.12.2015 Chemnitz Investive
19.12.2015 Chemnitz Amp 1
22.12.2015 Chemnitz Investive
10.12.2016 Chemnitz Gain
27.01.2012 Troisdorf Investive
07.03.2013 Troisdorf Investive
18.12.2008 Pfalz Gain
18.12.2008 Pfalz Investive
23.03.2011 Pfalz Amp 1
30.03.2012 Pfalz Gain
25.03.2013 Pfalz Investive
29.04.2014 Pfalz Investive
31.03.2015 Pfalz Investive
27.03.2015 Aachen Gain
27.03.2015 Aachen Investive
27.03.2015 Aachen Amp 1
27.03.2015 Aachen Investive
30.03.2015 Aachen Investive
30.03.2015 Aachen Investive
30.03.2015 Aachen Gain
14.03.2013 Essen Investive
26.03.2015 Essen Amp 1
26.03.2015 Essen Investive
22.03.2016 Essen Gain
23.12.2009 Essen Investive
23.12.2009 Essen Investive
23.12.2009 Essen Gain
25.03.2013 Essen Investive
30.04.2014 Essen Investive
16.04.2010 Hildesheim Gain
16.04.2010 Hildesheim Investive
01.01.2011 Hildesheim Gain
01.01.2011 Hildesheim Investive
24.03.2011 Hildesheim Gain
01.06.2013 Hildesheim Investive
20.03.2015 Hildesheim Investive
29.03.2016 Hildesheim Amp
12.06.2009 Osnabrück Investive
02.07.2009 Osnabrück Amp
21.08.2009 Osnabrück Investive
18.03.2011 Osnabrück Amp
16.02.2016 Osnabrück Investive
21.03.2013 Passau Investive
22.04.2014 Passau Gain
12.03.2015 Passau Investive
23.03.2016 Passau Investive
22.03.2013 Berlin Gain
29.03.2015 Berlin Amp

[benni55]

its 3 columns now. the second one is named "city program" and the last one "counting cases". sorry about that

[benni55]

Thanks Phuocam, that looks promising. :-) If you copy my new and longer table you will find that it works until there are two cases of "Gain" for one city. I need it to exclude those cases or otherwise I can´t just count the "here´s" and get a count overall.

But that´s probably the way to go. :-)

[Glenn Kennedy]

In D2, use this array formula:

=IF(ISNUMBER(IF(C2="Amp",MATCH(B2&"gain",$B2:$B$54&$C2:$C$54,0),"")),"Match","")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[Phuocam]

Try again ...

=IFERROR(IF(AND(C2="Amp";INDEX(B3:$B$1000;MATCH("Gain";C3:$C$1000;0))=B2);1;"");"")

[benni55]

Thanks again guys!

Glenns version did indeed work. At least that´s how I see it at the moment. Those array-magic is beyond me. ;-) I think I get most of it which is hopefully enough.
Thanks to you, sir.

Phuocams version did not work for my try here. I got only empty cells. But I couldn´t try it as heavily as Glenns version and will give you feedback on monday. :-)

You made my weekend!

[Glenn Kennedy]

i tried Phoucam's formula and (on the sample provided) it works just as well as mine.