Forum Discussion
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 type held for each period.
To do determine this i am trying to use a countifs and index match formula to count the number of funds with a balance greater than $0.00 for each fee type. The variables i am looking at are if the balance is greater than $0 for the month, and matches my designated fee type. However i am having some issues selecting the columns for the required month.
I have included a sample, can anyone please assist with how to resolve this?
Greatly appreciate any assistance.
Kind regards,
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.
5 Replies
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.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 kindly for your assistance, very much appreciated.
Kind regards,
