SOLVED

Help with Countif & Match formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2479359%22%20slang%3D%22en-US%22%3EHelp%20with%20Countif%20%26amp%3B%20Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2479359%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20which%20includes%20an%20account%20name%2C%20a%20column%20for%20the%20fee%20type%20and%20the%20balance%20held%20for%20each%20month.%20I%20am%20looking%20to%20do%20some%20reporting%20on%20the%20number%20of%20funds%20on%20each%20fee%20type%20held%20for%20each%20period.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20determine%20this%20i%20am%20trying%20to%20use%20a%20countifs%20and%20index%20match%20formula%20to%20count%20the%20number%20of%20funds%20with%20a%20balance%20greater%20than%20%240.00%20for%20each%20fee%20type.%20The%20variables%20i%20am%20looking%20at%20are%20if%20the%20balance%20is%20greater%20than%20%240%20for%20the%20month%2C%20and%20matches%20my%20designated%20fee%20type.%20However%20i%20am%20having%20some%20issues%20selecting%20the%20columns%20for%20the%20required%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20included%20a%20sample%2C%20can%20anyone%20please%20assist%20with%20how%20to%20resolve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreatly%20appreciate%20any%20assistance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2479359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2479409%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20%26amp%3B%20Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2479409%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%26nbsp%3BSee%20attached.%20Perhaps%20not%20the%20prettiest%20way%2C%20but%20it%20seems%20to%20work.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM((INDEX(%24A%242%3A%24H%2411%2C0%2CMATCH(K2%2C%24A%241%3A%24H%241%2C0))%26lt%3B%26gt%3B0)*(%24B%242%3A%24B%2411%3D%24J%241))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20%24J%241%20in%20the%20end%20is%20an%20absolute%20reference%20to%20a%20cell%20where%20you%20enter%20the%20fee%20type.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20that%20I%20also%20change%20the%20dates%20in%20the%20summary%20table%20to%20the%20dates%20used%20in%20the%20header%20of%20the%20fund%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2479553%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Countif%20%26amp%3B%20Match%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2479553%22%20slang%3D%22en-US%22%3EHi%20Riny_van_Eekelen%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20kindly%20for%20your%20assistance%2C%20very%20much%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EKind%20regards%2C%3C%2FLINGO-BODY%3E
Regular Contributor

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

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

Thank you kindly for your assistance, very much appreciated.

Kind regards,
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 again for your assistance with this, very much appreciated.

Kind regards