Forum Discussion
Solved
Help with Countif & Match formula
Hi All, I have a spreadsheet which includes an account name, a column for the fee type and the balance held for each month. I am looking to do some reporting on the number of funds on each fee ty...
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.
hi Riny_van_Eekelen
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.
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