Hi Guys
I'm trying to use Countifs on a contiguous range using multiple criteria as an array but the formula throws out an error.
If I individually choose the criteria range along with a single criteria , the formula works. In my situation I have 16 Columns on which I need to perform a Countifs along with 16 criterias , hence looking for an Array solution rather than adding Criterias 16 times
Any help is much appreciated.
Below is my formula which gives error
{=SUMPRODUCT(COUNTIFS(Date,$O9,Product,SoldWhat,SalesRep,$P9,Store,Where,Table1[[Online Sale]:[Achieved Margin]],$H$3:$K$3))}
Formula which works
=SUMPRODUCT(COUNTIFS(Date,$O9,Product,SoldWhat,SalesRep,$P9,Store,Where,Table1[Online Sale],$H$3,Table1[Cash Sale],$I$3,Table1[Not Discounted],$J$3,Table1[Achieved Margin],$K$3))
Attached is a sample file.
Thanks
Mysore
Bookmarks