(More) CountIfs type questions

[JKirstine]

I've been through various discussions on recreating something akin to the the Excel CountIfs function, but have been struggling for 2 days.

Basically, I need have a row be able to count any previous rows (it will be previous by default) that match certain criteria

So, for a given row, as long as its type <> "Online", I want to count records/rows where Cust ID = Cust ID, Type DOES = Online in a previous record, and the Purch date of the row to be counted is between the purchase date the row in question and 30 days prior. 

In the data below, the later occurrences of CustIDs 199003 and 135833 (I tried to bold type) would each show a count of 1 (assumedly in a new column so i can flag it later), as they each are <>Online, but there is row with a matching CustID, where Type = Online, and was within the previous 30 days. 

Purch Date APP	Purch Date-1	Purch Date less 30	Purchase#	Type	CustID
6/30/2021	6/29/2021	5/30/2021	2260787	Online	199003
7/1/2021	6/30/2021	6/1/2021	2153512	Online	135833
7/21/2021	7/20/2021	6/21/2021	2575005	Shop2	146651
7/21/2021	7/20/2021	6/21/2021	2871696	Shop1	190452
7/21/2021	7/20/2021	6/21/2021	2834699	Shop1	183225
7/21/2021	7/20/2021	6/21/2021	2934265	Shop1	151681
7/21/2021	7/20/2021	6/21/2021	2807218	Shop2	113738
7/21/2021	7/20/2021	6/21/2021	2967201	Shop2	127026
7/21/2021	7/20/2021	6/21/2021	2487600	Shop2	199003
7/21/2021	7/20/2021	6/21/2021	2693672	Shop2	160585
7/21/2021	7/20/2021	6/21/2021	2058022	Online	177808
7/21/2021	7/20/2021	6/21/2021	2866540	Online	111339
7/21/2021	7/20/2021	6/21/2021	2745257	Shop1	135833
7/21/2021	7/20/2021	6/21/2021	2602772	Shop1	116841
7/21/2021	7/20/2021	6/21/2021	2899620	Shop1	155846

[JKirstine]

I think solved my issue, at least in a proof of concept. If it works, I'll post the solution

[PhilipMannering]

This was not trivial, especially as batch macros are not my strong point. But may have solved it.

See attached,

With batch macro,

[FlorianC]

Hi @JKirstine,

In order to replicate the COUNTIF formula from Excel, I would recommend using the multi-row formula tool. You can easily group by customer ID and look at the values from the previous rows to determine whether the purchase was made online and within 30 days.

Please find attached an example workflow demonstrating how to to this.

Example workflowWorkflow results

[mceleavey]

@JKirstine ,

I did this with a simple multi-row formula.

Hope this helps,

M.

[JKirstine]

Thanks to everyone who provided a solution. I will look at the multi-row formula.

I was able to use a Filter + a Join to get the records I was eventually after, even though I didn't get the flag I was looking for as part of the original solution i had in mind (really need to stop thinking in Excel....LOL). I did make one change from the request in that I used a 14 day window via a new variable rather than the 30 day window I originally talked about. But, it does work and gives me what I need. This is just the proof of concept workflow, but in applying it to my larger dataset and regression testing, it was dead on.

Phillip - I will take a look at your solution as I have never attempted Alteryx macros before. This might be a good lesson for me. 

Have to say, I love this tool!