CountIFS using named range (Excel)

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

Sample.xlsx

Help

Accepted answers

LordNeilLord

Hey @Beginner

Here's an approach for you:

Bands.yxzp

ponraj

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

Solution for countifs (1).yxmd

All comments

LordNeilLord

Hey @Beginner

Here's an approach for you:

Bands.yxzp

ponraj

Here is another approach for you

Solution for countifs.yxmd

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.

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.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335