Excel Countif - actual counter

%3CLINGO-SUB%20id%3D%22lingo-sub-1498517%22%20slang%3D%22en-US%22%3EExcel%20Countif%20-%20actual%20counter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498517%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3Ecould%20you%20please%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Countif%20function%20in%20the%20column%20next%20to%20database%20and%20it%20is%20giving%20me%20just%20the%20total%20counts%20but%20is%20there%20any%20other%20function%20which%20can%20give%20me%20exact%20counter%20%3F%3C%2FP%3E%3CP%3EExample%20attached%20-%20Column%20C%20is%20what%20i%20am%20looking%20for%20but%20i%20have%20to%20do%20it%20manually%2C%20is%20there%20any%20function%20which%20can%20help%20me%3F%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1498517%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498587%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countif%20-%20actual%20counter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498587%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F714308%22%20target%3D%22_blank%22%3E%40Filip_Rozkosny%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20410px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F202160iC30960A50D4726AF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498641%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countif%20-%20actual%20counter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498641%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F714308%22%20target%3D%22_blank%22%3E%40Filip_Rozkosny%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20%3CEM%3Ethink%3C%2FEM%3E%20this%20is%20what%20you're%20looking%20for.%20I%20changed%20the%20formula%20you%20had%20so%20that%20it's%20only%20looking%20at%20column%20A%20for%20the%20current%20and%20preceding%20rows.%20The%20way%20you'd%20written%20it%20it%20was%20always%20comparing%20the%20value%20in%20the%20current%20row%20with%20all%20rows.%3C%2FP%3E%3CP%3EYour%20original%3A%20%3CSTRONG%3E%3DCOUNTIF(A%3AA%2CA2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ECorrected%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CSTRONG%3E%20%3DCOUNTIF(%24A%242%3AA2%2CA2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20absolute%20reference%20%3CSTRONG%3E%24A%242%3C%2FSTRONG%3E%20anchors%20the%20range%2C%20and%20then%20allows%20the%20other%20(relative)%20one%2C%20%3CSTRONG%3EA2%2C%3C%2FSTRONG%3E%20to%20change%20as%20it%20is%20copied%20down%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20am%20making%20the%20assumption%20that%20this%20is%20what%20you%20wanted%20because%20it%20does%20deliver%20the%20number%20you%20seem%20to%20want.%20You%20didn't%20actually%20describe%20HOW%20you%20arrived%20at%20your%20%22Requested%20data%22%20manually.%20So%20I%20made%20an%20inference%20which%20did%20come%20up%20with%20the%20same%20answers....that%20still%20could%20be%20coincidence.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500746%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countif%20-%20actual%20counter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500746%22%20slang%3D%22en-US%22%3E%3CP%3Emany%26nbsp%3Bthanks%2C%26nbsp%3Bclear%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1500747%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Countif%20-%20actual%20counter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1500747%22%20slang%3D%22en-US%22%3Ethanks%2C%20clear%2C%20muchly%20appreciated.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Dear all,

could you please help me?

 

I am using Countif function in the column next to database and it is giving me just the total counts but is there any other function which can give me exact counter ?

Example attached - Column C is what i am looking for but i have to do it manually, is there any function which can help me?

Many thanks in advance.

5 Replies
Highlighted

@Filip_Rozkosny 

That is

image.png

and drag it down

Highlighted

@Filip_Rozkosny 

 

I think this is what you're looking for. I changed the formula you had so that it's only looking at column A for the current and preceding rows. The way you'd written it it was always comparing the value in the current row with all rows.

Your original: =COUNTIF(A:A,A2)

Corrected:      =COUNTIF($A$2:A2,A2)

 

The absolute reference $A$2 anchors the range, and then allows the other (relative) one, A2, to change as it is copied down the column.

 

But I am making the assumption that this is what you wanted because it does deliver the number you seem to want. You didn't actually describe HOW you arrived at your "Requested data" manually. So I made an inference which did come up with the same answers....that still could be coincidence.

Highlighted

many thanks, clear @Sergei Baklan 

Highlighted
thanks, clear, muchly appreciated.
Highlighted

@Filip_Rozkosny , you are welcome