Searching a column for specific text to return a number of items found

  • hello everybody,


    at a charity i currently volunteer for, records are kept of regular donors in excel. on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...


    the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.


    i would like help to set up a function/formula to count the number of, for example, "Monthly" entries in the frequency column in the sheet containing the main list.


    thanks for your time!

  • Re: Searching a column for specific text to return a number of items found


    If the sheet is Sheet1 and the Frequency column is 'A' then I would simply put


    =countif(Sheet1!A:A,"Monthly")

  • Re: Searching a column for specific text to return a number of items found


    thank you for the quick response.


    the sheet is called "List of DD Donors" so i tried: =COUNTIF("List of DD Donors"!E:E,"Monthly") and also without the speech marks. neither worked. what's wrong?

  • Re: Searching a column for specific text to return a number of items found


    Quote from .broken.

    thank you for the quick response.


    the sheet is called "List of DD Donors" so i tried: =COUNTIF("List of DD Donors"!E:E,"Monthly") and also without the speech marks. neither worked. what's wrong?


    You were very close: You need single quotes around the sheet name:


    =COUNTIF('List of DD Donors'!E:E,"Monthly")



    Brian

    >DISABLE DALEK WITH SONIC SCREWDRIVER
    The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...


    The Dalek notices your presence for the first time, and reacts normally.
    "EXTERMINATE! EXTERMINATE!"


    *** You have been exterminated ***

  • Re: Searching a column for specific text to return a number of items found


    Cheers Brian!


    hehe, i now have a worksheet is a ' in its name! "Cancelled DD's"
    typical.


    any ideas?

  • Re: Searching a column for specific text to return a number of items found


    Here's another idea:


    =COUNTIF('Cancelled DD''s'!E:E,"Monthly")


    That's a pair of single quotes after DD, not a double quote.



    Brian

    >DISABLE DALEK WITH SONIC SCREWDRIVER
    The batteries have gone flat. I told you that you should have changed them after you spent ten hours debugging it in the TARDIS. But did you listen? Noooooooo...


    The Dalek notices your presence for the first time, and reacts normally.
    "EXTERMINATE! EXTERMINATE!"


    *** You have been exterminated ***

Participate now!

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