5 Replies Latest reply on Nov 1, 2016 11:29 AM by michael petrillo

# Countif on multiple columns & get the result for each row

Hi All,

I've tried searching this on forum, But couldn't find anything to do Countif on multiple column measures

What i want is to check 19 columns with a given a target from a another column & count - How many times value from 19 column was less than target column

I have data in below excel format, in which Countif(Data range, <Target) - Provides me the count - How many columns are less than target

Can anyone help me to - How can i do this in Tableau? I think - IFF formula would be too lenghty to use

It seems very easy in excel, But i am not able to do it in Tableau

 A B C D E F G H I J K L M N 2 PLAN Target Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 Day 8 Day 9 Countif Countif Formula 3 Beginning Inventory 520 514 490 685 685 659 629 605 582 518 3 =COUNTIF(D3:L3,"<"&C3) 4 Demand 10 24 0 0 26 30 24 23 64 0 3 =COUNTIF(D4:L4,"<"&C4) 5 DOS 30 30 29 38 37 36 35 34 33 32 1 =COUNTIF(D5:L5,"<"&C5)

Regards,

Sidhesh

• ###### 1. Re: Countif on multiple columns & get the result for each row

So if you have this sample data in Excel, you didn't post a sample Excel file because? Having said this, have you tired shaping your 19 columns using parameter based filters, and then doing a count on the result? In my country we have a saying: There's more than one way to skin a cat. [I have no idea where that comes from.] The point being you haven't given us enough information to be able to help you.

--Shawn

• ###### 2. Re: Countif on multiple columns & get the result for each row

Hey Shawn - The actual data is in millions of records & hence can't post it here.

Attached is the sample excel file which has measures in 8 Date columns & i want to count if any of these 8 columns are less than the target for each of the plan rows..

I've added the countif formula & also highlighted the column values which were less than target & its count is in last column.

I didn't get your comment on "Shape all columns using parameters" . Can you pls tell me  in detail- what it will do & how to do it?

Regards,

Sidhesh

• ###### 3. Re: Countif on multiple columns & get the result for each row

Sldhesh, I wasn't able to come up with a way to do what I think you are trying to do. Maybe some of the other folks can figure it out.

--Shawn

• ###### 4. Re: Countif on multiple columns & get the result for each row

Sidesh,

Is the attached workbook close to what you want?  It replicates the countif funciton.  I added an ID column so I could ensure that each row shows in the cross tab.  (Without the ID, the count calculation is still correct as it is performed at a row level, but the other values are likely meaningless in aggregate).

The highlighting is much more difficult, if not impossible, as Shawn indicated, given the shape of the data.  This is because, in your data, each Day is a different measure and there is no way to reference [Measure Value] in a calculation which could be used to encode color for highlighting.  If Day was simply a dimension and the measure was a single column, then the highlighting would be relatively easy.

Things would be much easier, if the data looked like this:

 Planning Type Target Day Value Beginning Inventory 515 Day 1 514 Demand 0 Day 1 24 DOS 20 Day 1 30 Beginning Inventory 515 Day 2 490 Demand 0 Day 2 0 DOS 20 Day 2 29 Beginning Inventory 515 Day 3 685 Demand 0 Day 3 0 DOS 20 Day 3 38

etc...

etc...

Regards,

Joshua

• ###### 5. Re: Countif on multiple columns & get the result for each row

This solution requires making each value a variable, which generally will not work.

Is there a way to do a count if with a date range?