# COUNTIFS based on multiple criteria that tracks changes in two columns

1. ## COUNTIFS based on multiple criteria that tracks changes in two columns

Hello,
See picture below:

COUNTIFS.png

I wish to create a COUNTIFS formula that functions as follows:

a. Counts how many times values in Column D & E reappear.
b. Resets to 0 if Criteria X changes. (Changed marked with yellow.)
c. Resets to 1 if Criteria Y changes. (Changes marked with blue.)
d. Resets to 0 if both criteria changes.

Multiple Name+Thing combinations with their own criteria X and Y exist. This is why this must be included in the formula's criteria. I've hidden some sample rows with Anne+Shirt instead of Bob+Car as an example of this. Bob+Thing is independent of Anne+Shirt.

Criteria X goes from 0 to ∞.
Criteria Y only goes from 0 to 1.
All changes in Criteria X and Y are in consecutive increasing increments of 1. I.e. 1, 2, 3, 4, 5, etc...

I've been able to make the formula work for all points except that for point (c.), where it should reset to 1 if Criteria Y changes with:
In N3: =COUNTIFS(\$D\$3:D3;D3; \$E\$3:E3;E3; \$F\$3:F3;F3; \$G\$3:G3;G3)-IF(F3>0;1;0)

(The IF(F3>0;1;0) is to make the formula reset to 0, and start at 1 at N3 (since no changes happen at N3, like 0 to 1, 1 to 2, etc..).

See attached Excel sheet.
It's easier to understand when comparing the source data to a visual representation of the desired end result.

Thanks

EDIT: See last post by AliGW on page 2 for the solution.

2. ## Re: COUNTIFS that tracks changes in two columns

=COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3)-(F3>0)+(G3<>G2)*(D3=D2)

3. ## Re: COUNTIFS that tracks changes in two columns

Originally Posted by Bo_Ry
=COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3)-(F3>0)+(G3<>G2)*(D3=D2)
Hi, thank you for your response, but this doesn't work with multiple Name+Thing values. See picture below where I've un-hidden some rows in this sheet with Anne+Shirt.

In the actual sheet I'll use this formula for there are hundreds of Name+Thing combinations. Like for instance Bob+Shirt, Bob+Whatever, and Michal+Whatever, etc.etc...
Because of this I'd try avoid consecutive relations like (G3<>G2)*(D3=D2).

This problem is already solved in the first part of my attempt formula: =COUNTIFS(\$D\$3:\$D3;\$D3;\$E\$3:\$E3;\$E3 ...

COUNTIFS2.png

4. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Testing Bo_Ry's formula after the rows containing 'Anne' and 'Shirt' are unhidden, it seems to work. The only row in which it does not return the value listed in column L is row 30 and looking at point d. in post 1 (Resets to 0 if both criteria changes) it seems as if Bo_Ry's formula is again correct.
Let us know if you have any questions.

5. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

When I say 'When a criteria changes' I mean that a value change happen at a per-row basis.
For Anne: Criteria X changes from 0 to 1 at row 22. Then Criteria Y changes from 0 to 1 at row 30.
Therefore only Criteria Y changed at row 30, and therefore N30 should be 1 according to point c.

6. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

=COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3)-(F3>0)+(G3<>G2)-ISTEXT(G2)

7. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

This leads to the formula stop counting between N31 and N32, thus both N31 and N32 (for Bob) is 2. N31 should be 1.
Column L shows the desired results. See picture below.

I'd recommend avoiding any sequential dependencies that refer to either the previous or next row for calculations, explained in my post before the previous.

27.05.19 2.png

8. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

These large images are just clutter - please just upload the workbook. If you must post visuals, please cut them down to just a view of the relevant area (e.g. rows 26 to 36) so that they take up less room. The thread is getting difficult to follow. Thanks!

9. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

I apologize about not avoiding sequential and still get the error.

I still use sequential because I have no idea how to track change from 0 to 1 or 1 to 0, without comparing with sequential cell.
This is my last try
N2 =IF(G2<>G3;1;COUNTIFS(\$D\$3:\$D3;\$D3;\$E\$3:\$E3;\$E3;\$F\$3:\$F3;\$F3;\$G\$3:\$G3;\$G3)-(F3>0))

Good luck and hope that you get others help.

10. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Originally Posted by Bo_Ry
N2 =IF(G2<>G3;1;COUNTIFS(\$D\$3:\$D3;\$D3;\$E\$3:\$E3;\$E3;\$F\$3:\$F3;\$F3;\$G\$3:\$G3;\$G3)-(F3>0))
Hi, thank you for your help but this won't work if I for instance add two Criteria Y changes in a row. In the attached sheet I've now made Criteria Y count 1 from G31, then N31 becomes 0, when it should be 1. See picture.

28.05.19 2.png

I still use sequential because I have no idea how to track change from 0 to 1 or 1 to 0, without comparing with sequential cell.
Just for clarification, all changes increase by increments of 1, never decrease. See additional info in original post:
Criteria X goes from 0 to ∞.
Criteria Y only goes from 0 to 1.
All changes in Criteria X and Y are in consecutive increasing increments of 1. I.e. 1, 2, 3, 4, 5, etc...

11. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

You said that if both criteria change, 0 should be returned. Why should N30 and N31 be 1? This breaks the logic you gave at the outset:

a. Counts how many times values in Column D & E reappear.
b. Resets to 0 if Criteria X changes. (Changed marked with yellow.)
c. Resets to 1 if Criteria Y changes. (Changes marked with blue.)
d. Resets to 0 if both criteria changes.
Please explain again and in detail what the logic should be.

12. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Originally Posted by AliGW
You said that if both criteria change, 0 should be returned. Why should N30 and N31 be 1? This breaks the logic you gave at the outset:

Please explain again and in detail what the logic should be.
Name+Thing is it's own criteria. Bob+Car is independent of Anne+Shirt. This is included in the first part of my sample formula =COUNTIFS(\$D\$3:D3;D3; \$E\$3:E3;E3; ...
Multiple Name+Thing combinations with their own criteria X and Y exist. This is why this must be included in the formula's criteria. I've hidden some sample rows with Anne+Shirt instead of Bob+Car as an example of this.
For Anne+Shirt, only Criteria Y changes from 0 to 1 at G30. Criteria X stays at 1 from F22 to F30. Therefore N30 should be 1 according to c.
For Bob+Car, only criteria Y changes from 0 to 1 at G31. Criteria X stays at 4 from F29 to F31. Therefore N31 should be 1 acfording to c.

13. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Ah, I see. OK - I'll have a think.

14. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Try this:

=IFERROR(IF(LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$G\$2:\$G2)<G3,1,COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3)-(F3>0)),1)

15. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

This solves point b) c) and d), but misses point a) at N32 and N33.
28.05.19 3.png

The thing that's so annoying with this problem is that the original formula =COUNTIFS(\$D\$3:D3;D3; \$E\$3:E3;E3; \$F\$3:F3;F3; \$G\$3:G3;G3)-IF(F3>0;1;0)
already solved 98% of the problem. It works for every case, no matter how complex any additional Name+Thing combinations or Criteria X and Y changes are, except that it misses point c) and resets to 0 when Criteria Y changes (and thus all counting after that happens)

16. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

I am sure you can tweak it to get what you want. Just 2% to go ... Let us know when you crack it.

17. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Just noticed an anomaly. You said this:

Criteria Y only goes from 0 to 1.
So why are cells G34 onwards 2? And what triggers a change in Y?

18. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Originally Posted by AliGW
Just noticed an anomaly. You said this:

So why are cells G34 onwards 2? And what triggers a change in Y?
My apologies. I added two Criteria Y changes in row to demonstrate why consecutive relations likely won't work, and forgot to delete the second.
Criteria Y should only go from 0 to maximum 1.

Attached Excel sheet is updated to show this:

19. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

For clarification:

If anyone finds the simplified sheet being used in this thread to be be too arbitrary and wish further details, see attached sheet of the actual purpose I'll use this formula for.
If not please ignore this post.

This is an inspection log where we note down which parts we receive that are defective.
Column G counts consecutive positive inspections, and resets to 0 if we find a defect part. The column G formula is what I wish to figure out.
The catch here, is that we sometimes find defect parts in production that we missed in inspections. The last detected defect is listed in column F.
When this happens, I wish the counting to start at 1, because a Positive inspection is often completed after that defect part was discovered. (See row 2411 and 2475. Between these inspections a defective part (column F) was discovered.)
Column H = Criteria X, and Column L = Criteria Y in the other simplified workbook basically. (Column K and L is just me messing around with formulas. K and L can be deleted).

20. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

I don't understand your expected results in rows 34 and 35. Please explain why these are 1.

In your latest sample (NOT the real workbook), in Q3 copied down:

=IFERROR(IF(AND(LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$F\$2:\$F2)=F3,LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$G\$2:\$G2)=G3),COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3)-MIN(1,COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3,\$Q\$2:Q2,0)),IF(LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$G\$2:\$G2)<>G3,1,0)),1)

This is not correct in rows 34 and 35 based on your desired outcome which I simply do not understand in terms of the logic.

21. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Hi,

Should've also updated these when I changed that Criteria Y placement. Row 34 and 35 should be 4 and 5 of course.

22. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Where is the 'correct' sheet?

See column Q:

Excel 2016 (Windows) 32 bit
L
M
N
O
P
Q
2
Desired end result: My attmept:
3
1
1
1
4
2
2
2
5
3
3
3
6
4
4
4
7
5
5
5
8
1
1
1
9
6
6
6
10
7
7
7
11
8
8
8
12
2
2
2
13
0
0
0
14
1
1
1
15
2
2
2
16
3
3
3
17
4
4
4
18
5
5
5
19
6
6
6
20
7
7
7
21
8
8
8
22
0
0
0
23
9
9
9
24
10
10
10
25
0
0
0
26
1
1
1
27
0
0
0
28
1
1
1
29
0
0
0
30
1
1
1
31
1
1
1
32
2
1
?This should be 2
2
33
3
2
?This should be 3
3
34
1
3
4
35
1
4
5
36
0
0
0
37
1
1
1
38
2
2
2
 Sheet: Sheet1

23. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Let me know if it's worked for you - trying to work it out has given me a thumping headache!

24. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Hi,

Wow, that formula is a little to digest.
That works great though for the sheet I've given you.
Only thing is if I mess with the sheet and make both Criteria X and Y change at the same time at row 31, the formula becomes 1.

I think this whole problem could've been written better by myself from the start, and include a example of every point a) b) c) and d) with multiple Name+Thing combinations.
Sorry for the headache:PP Getting one myself.

I think this thread is getting close to impossible to follow now, due to small mistakes and misunderstandings here and there.
Do you think it's better if this thread is deleted and re-made? I will rewrite the thread and include a better example that takes every point into consideration.
I could also just update the original post and my latest post to include a better sheet though. I won't be able to do that today though.

25. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

No, absolutely not! We do not allow more than one thread on each issue - you must continue here.

Anyone wishing to help will need to respond here. I have to confess, the various errors you have made along the way have not helped ...

I might have a look at the latest file once I have recovered from the last one.

26. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Try this:

=IFERROR(IF(AND(LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$F\$2:\$F2)=F3,LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$G\$2:\$G2)=G3),COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3)-MIN(1,COUNTIFS(\$D\$3:\$D3,\$D3,\$E\$3:\$E3,\$E3,\$F\$3:\$F3,\$F3,\$G\$3:\$G3,\$G3,\$Q\$2:Q2,0)),IF(AND(LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$F\$2:\$F2)<>F3,LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$G\$2:\$G2)<>G3),0,IF(LOOKUP(2,1/(\$D\$2:\$D2=D3)*(\$E\$2:\$E2=E3),\$G\$2:\$G2)<>G3,1,0))),1)

27. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

I've amended the formula to do what you want. See post #26 where I have attached your file.

28. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Hey,

Seems like it works on all points a) b) c) d)
Thanks a lot AliGW. I'll try this formula out on my original 3000+ row sheet and see how it works in the coming weeks. I'll try to modify it if I find some irregularities.

Thanks again. +1

29. ## Re: COUNTIFS based on multiple criteria that tracks changes in two columns

Thank heavens for that!

I was about to throw in the towel.

There are currently 1 users browsing this thread. (0 members and 1 guests)