Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

COUNTIFS with several conditions

timol
8 - Asteroid

Dear all,

 

I have the following data structure

 

timol_0-1616671841068.png

 

Now I would like to just count the Expenses for a total year to get the structure below 

 

timol_1-1616671892634.png

 

 

Does anyone have an idea how to do that?

In excel I would work with a COUNTIFS function.

 

Thank you so much!

9 REPLIES 9
Emil_Kos
17 - Castor
17 - Castor

Hi @timol,


I have prepared a workflow for you.

 

Emil_Kos_3-1616672365109.png

 

 

The first summarize shows sum:

Emil_Kos_4-1616672373325.png

The second one show countif:

Emil_Kos_5-1616672392309.png

 

 

 

timol
8 - Asteroid

Thank you!!!

estherb47
15 - Aurora
15 - Aurora

Hi @timol 

 

Adding a bit onto @Emil_Kos solution, you can put the count and the sum in the same Summarize tool. 

 

Company and Year fields should be group by, and then put expenses in twice, one as sum and one as count.

 

Cheers!

Esther

timol
8 - Asteroid

Thank you so much!

Sorry but One more question: How can I tell Alteryx to create a new column filled with the value of column "A", but if column A is null it should take the value of column "B"?

estherb47
15 - Aurora
15 - Aurora

If you want to grab the number from what’s underneath, you could use a Muti Row formula tool. 

if the value is blank, look to the row below. 

you’d likely need to sort by year and then by company to make this method work. 

let me know if that helps. 

cheers!

esther

timol
8 - Asteroid

Thank you so much!


So there is no way using just the "normal" formula tool stating like in excel =IF(B1;"";C1;B1) ("if B1 is empty use C1 otherwise use B1)?

 

estherb47
15 - Aurora
15 - Aurora

You absolutely could, if the data for A, B, C were in the same row. It's when they're not in the same row that we need to get into the multi row formula tool

 

Cheers!

Esther

timol
8 - Asteroid

Would it also be possible to count values for e.g. 2019 and all years before that?

 

So assume that in the table above I would have data for the years 2015-2020. And I would like to accumulate values in every year.

But (for whatever reason) one company has only values for 2015, 2018, 2019 and 2020.

I still want to have values for 2017-2019.

 

So assume in the example below I would like to show values for 2016 and 2017 too (that are constant in comparison to 2015).

 

A112.01.2020
A212.01.2020
A2312.01.2020
A2112.01.2019
A2112.01.2018
A2112.01.2015
A2112.01.2015
B2312.01.2019
B4212.01.2020
B2312.01.2019
C112.01.2019
timol
8 - Asteroid

Does anyone have an idea?

 

The output for eg. company A I would like to achieve would look like this:

 

A201542
A201642
A201742
A201863
A201984
A2020110
Labels