Using countif to count word in specific colums only

  • Hi,


    I am using the countif formula in column E to count the number of times the word "WORKING" appears in each row of a worksheet, the formula works but there is one problem.


    I only want the formula to count the number of times the word "WORKING" appears in the columns with SHIFT STATUS as their headings; at present the formula will also count if the word "WORKING" also appears in the NOTES columns which i don't want it to do.


    Thanks in advance for any ideas.


    Ade

  • Re: Using countif to count word in specific colums only


    Quick and dirty - just use COUNTIF() on the specific cells to see if they contain "WORKING"


    [f]
    =COUNTIF(H3, "WORKING") + COUNTIF(M3, "WORKING") + COUNTIF(R3, "WORKING")
    [/f]

  • Re: Using countif to count word in specific colums only


    Thanks cytop, i was thinking of something like that but the formula could potentially be very long and was wondering how else it could be approached; probably one for vba i guess.


    Thanks again.



    Ade

  • Re: Using countif to count word in specific colums only


    My example was based on what you said in your post... if the number of 'staff blocks' is variable, you could do sometihng like this...


    This needs to go into it's own module in the VBA editor - make sure the 'Option compare...' statement is included as this allows non-case sensitive text comparisons. This provides a new Excel 'function' called CountOccurrences'. You use it exactly the same as any other Excel formula


    [f]
    =CountOccurrences(Startcell, EndCell, Text)
    [/f]


    StartCell should be the first cell to check (Column H in your example), Endcell can be any cell on the same row... It also accepts the text to compare so is not tied to just searching for "Working"

  • Re: Using countif to count word in specific colums only


    No you don't need VBA, try this formula in E3 copied down


    =COUNTIFS(F$2:EN$2,"Shift Status",F3:EN3,"Working")

  • Re: Using countif to count word in specific colums only


    As always, fantastic ideas which are greatly appreciated, thanks again to cytop & daddylonglegs.


    Ade

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!