Tricky counting formula

[mjones]

Hi All,

At > 6000 rows, it’s impossible to manually count so I hope a formula genius out there can help.

All cells are general format with text.

Count how many situations occur with rows with the same text in columns B, D & E that also have a row with x in column G and another row with y in column G.

The case below would count 1 for rows 1 and 2 matching the scenario, but rows 3 and 4 do not count because even though B, D & E match, there are not two rows where one has an x and one has a y in column G.

B D E G
1 AB CD EF x
2 AB CD EF y
3 GH KL TY z
4 GH KL TY y

The above is in the 'Songs in Playlists'! tab, but the count/answer will be in another tab.

Thank you for considering my formula,

Michele

 

[mjones]

 

[KRice]

Oh good!...so those numbers make sense to you? I'll give some thought to a more efficient way.

 

[mjones]

To confirm, yes numbers are correct - formula works! The speed is just unworkable and most likely will worsen when the source data is doubled. I didn't know a formula could slow Excel so much but it makes sense. Crossing fingers. Good night.

 

[KRice]

Good...about the numbers being correct. And yes, some formulas can make a worksheet sluggish. I've reviewed your earlier comment---I guess there is no need to sum the number of rows where B,D,E match with one of the G=x or G=y terms because you've said that there would ever only be one or none with an x, and one or none with a y. And that appears to be the case, so I think there are some other approaches to try out. Just to confirm then, for any chosen B,D,E entry, you're expecting the number of pairs (after considering the G=x and G=y criteria) will be either 0 or 1....and you want the sum of those 0's and 1's for the entire set of B,D,E on the 'Songs...' worksheet?

 

[mjones]

Now you have it! Formula works and at a fast speed!

A thing to note is that column G has a drop down from a list and thus won't have a typo. Plus, you are correct in that any song will have only unique items in column G. If I put more than one of the same playlist in column G (e.g. easy listening or 80's), other sums will give me a warning and I'll find it that way, i.e. sums in iTunes won't match those in the spreadsheet because iTunes won't let you put a song in more than one playlist.

I cannot thank you enough. I do hope you enjoyed the tricky question. All the best!

Michele

 

[KRice]

Michele, I noticed that about column G...a good idea to use a drop down list for consistency. And in testing out the formula--and making some targeted changes to the source table to confirm counting accuracy--I saw the TRUE/FALSE flags alert. So I think you could safely avoid performing the UNIQUE operation, as it would not actually eliminate anything because of the other checks. In that case, a minor tweak:

=LET(src,'Songs in Playlists'!B$2:G$5748, x,TEXTBEFORE(A23,": "), y,TEXTAFTER(A23,": "), g,INDEX(src,,6),
bde,INDEX(src,,1)&"|"&INDEX(src,,3)&"|"&INDEX(src,,4), fsx,FILTER(bde,g=x,""), SUMPRODUCT((g=y)*ISNUMBER(MATCH(bde,fsx,0))))

You're very welcome...I appreciate your patience working through the formula evolution.

 

[mjones]

New formula works and is fast. I think I should be thanking you for your patience. Thank you!