Help with Countif & Match formula

[calof1]

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,

[#2 · Accepted solution]

@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.

[#3]

Hi Riny_van_Eekelen,

Thank you kindly for your assistance, very much appreciated.

Kind regards,

[#4]

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.

[#5]

@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. 

[#6]

Hi Riny_van_Eekelen

Thank you again for your assistance with this, very much appreciated.

Kind regards