vegasbaby207
Board Regular
- Joined
- Nov 13, 2008
- Messages
- 53
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
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