Forum Discussion
Help with Countif & Match formula
calof1 See attached. Perhaps not the prettiest way, but it seems to work.
=SUM((INDEX($A$2:$H$11,0,MATCH(K2,$A$1:$H$1,0))<>0)*($B$2:$B$11=$J$1))where $J$1 in the end is an absolute reference to a cell where you enter the fee type.
Note that I also change the dates in the summary table to the dates used in the header of the fund table.
calof1 See attached. Perhaps not the prettiest way, but it seems to work.
=SUM((INDEX($A$2:$H$11,0,MATCH(K2,$A$1:$H$1,0))<>0)*($B$2:$B$11=$J$1))
where $J$1 in the end is an absolute reference to a cell where you enter the fee type.
Note that I also change the dates in the summary table to the dates used in the header of the fund table.
Hope you are well.
I have noticed that i have another tab which is very similar to this, however has subtotals in the column A (Fund). Is it possible to add another criteria to the formula which would work the same as this but just exclude the rows which have total in the name?
Greatly appreciate your insights.
calof1 Well, it's not getting any prettier, but try this:
=SUM((INDEX($A$2:$H$12,0,MATCH(K2,$A$1:$H$1,0))<>0)*($B$2:$B$12=$J$1)*NOT(ISNUMBER(FIND("total",$A$2:$A$12))))Have added the part that starts with *NOT(ISNUMBER....... This will FIND the rows that include the word total in column A and set the value to FALSE ( NOT(ISNUMBER) ) when it does, thereby excluding it from the count.
- Hi Riny_van_Eekelen
Thank you again for your assistance with this, very much appreciated.
Kind regards