CountIfs with OR lookup from a table

[vegasbaby207]

Hi,

I have a working CountIfs formula which is referencing a table (CurrentSeason) to facilitate multiple OR criteria:

{=SUM(COUNTIFS(tblItemsAU[Season],CurrentSeason[Prefix],tblItemsAU[QuantityOnHand],0))}

The formula counts the number of items in a table where QuantityOnHand is zero, and the product belongs to one of our current season range. It works as desired.

In an adjacent cell, I have another similar formula which does the same thing for Previous season ranges:

{=SUM(COUNTIFS(tblItemsAU[Season],PreviousSeason[Prefix],tblItemsAU[QuantityOnHand],0))}

As you can see, the formula is using a different table to build the OR criteria.

To make the solution a little more elegant (and scalable), I'd like to consolidate the two tables (CurrentSeason and PreviousSeason) into a single table, with a new column indicating if the product is Current or Previous season. This is easily done obviously. However, I am unsure if I can amend the above formulas so that the array can be restricted by another column.

Hope this makes sense.

Thanks

 

[rondeondo]

Hi V

yes, add another pair of range,criteria for the current/previous column.
ie.
{=SUM(COUNTIFS(tblItemsAU[Season],CurrentSeason[Prefix],tblItemsAU[QuantityOnHand],0,tblItemsAU[CurrentPrevious],"Current"))}
{=SUM(COUNTIFS(tblItemsAU[Season],CurrentSeason[Prefix],tblItemsAU[QuantityOnHand],0,tblItemsAU[CurrentPrevious],"Previous"))}