SOLVED

# Help with Countif & Match formula

Regular Contributor

# 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,

5 Replies
best response confirmed by calof1 (Regular Contributor)
Solution

# Re: 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.

# Re: Help with Countif & Match formula

Hi Riny_van_Eekelen,

Thank you kindly for your assistance, very much appreciated.

Kind regards,

# Re: Help with Countif & Match formula

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?

# Re: Help with Countif & Match formula

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

# Re: Help with Countif & Match formula

Hi Riny_van_Eekelen

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

Kind regards