Alteryx and Excel style countifs/sumifs with dates criteria

Hey, I have a simple problem as I want to use countifs and sumifs functions in Alteryx. I have two datasets and I need to find out how many times a certain corporation ID appears in other dataset in a given time period.

Dataset1 is like this:

Column1=CorporationID, Column2= date1, Column3=date2.

Dataset2 is like this:

Column1=CorporationID,Column2=date3,Column3=price.

Now I first want to know how many times corporationID in dataset1 appears in dataset2 based on a condition that date3 falls between date1 and date2.

Second, I want to know what is the sum of "price" based on corporationID and certain daterange. I attached an excel and I would like to reproduce those results in Alteryx.

Excel has both example datasets.

example.xlsx

Common Use Cases

Accepted answers

jdunkerley79

Fairly simple adjustment including the Date1 and Date2 in the Summarise Group and in the second Join.

Attached updated sample.

SumIfs.yxmd

All comments

jdunkerley79

Probably the easiest way is:

- Use a Join Tools to join the two dataset (join on Corporation ID)

- Use a filter tool to pick the rows where date3 is between date1 and date2

- Use a summarise tool to create the Count and Sum

- Finally you need to get back the rows which have been lost so I would join back to the original DataSet1 and then union the unjoined rows.

Sample Attached

SumIfs.yxmd

aleksi555

Hey, thanks for your help. I forgot to add that in my real data I have corporationid:s many times in both datasets so im not sure can i use "join" then?

Best regards.Aleksi

aleksi555

Hey, I attached renewed dataset where there are few same corporationsID:s in both datasets.

example.xlsx

jdunkerley79

Fairly simple adjustment including the Date1 and Date2 in the Summarise Group and in the second Join.

Attached updated sample.

SumIfs.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703