CountIFS using named range (Excel)

[Beginner]

Dear All, 

I am completely new to alteryx and trying hard to get something similar using in Excel which is the use of Countif along with named ranges, below is the expression used in the attached sample "Sheet1" from column F to J row number 7 to 15:

COUNTIFS(Band1,">=" &A7,Band1,"<=" &B7)

Sheet2 - contains my named ranges which I have used in above equation to identify the number of years in each band based on column A  (BeginYear) and B (EndYear).

Do you think above is doable in Alteryx? I am really struggling please if someone can kindly shed some light, appreciated in advance. 

Kind regards

[LordNeilLord · Accepted solution]

Hey @Beginner

Here's an approach for you:

[ponraj]

Here is another approach for you

 

[Beginner]

LordNeilLord - Thank You very much for your prompt response, that was swift and spot on, I am just trying to test it a bit more but seems like a perfect solution, thanks again.

[LordNeilLord]

Hey @Beginner

No worries :)

If you're happy with...mark as accepted and close the thread :)

Neil

[Beginner]

Hi ponraj, Thanks for your response, Your solution is quite simpler but if you can check row 7 in Your screen shot for BeginYear 2010 and EndYear 2014 - Band 2 and Band 1 are populated which needs to be but with the wrong value instead of 4 in Band 2 and 1 in Band 1, I am evaluating it and trying to understand why only that row has wrong values in the required columns.

Thanks very much for looking into the request, appreciated.

[Beginner]

Sorry completely forgot it, thank You, I will mark it straight away, You are a star Sir, thank you once again.

[ponraj · Accepted solution]

Hi @Beginner, Begin and end year table has duplicate entry for the range 2010 to 2014.  You will get the correct results if you run the workflow after removing duplicate range 2010-2014.

Attaching the workflow after removing the duplicate range. 

BeginYear	EndYear
1973	1977
2011	2014
2007	2010
2010	2010
2001	2014
2010	2014
2005	2009
2006	2011
2010	2014

[Beginner]

Hi Ponraj,

Accept my apologies for the delayed response, You are right, my mistake, I have identified that as well, thank You so much, appreciated your help, support and your solution.

I have used Your approach and LordNeilLord both seems like working great, thank You once again.

Kind regards