+ Reply to Thread
Results 1 to 9 of 9

sum formula to look for text value in column A & then count all cells that have EitherA/B

  1. #1
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Wink sum formula to look for text value in column A & then count all cells that have EitherA/B

    Hi i am looking for a sum formula!

    I was hoping one of you guys as EXCEL FORUM could help me!

    Basically i have a number of text values in column A just for example GCSE, AQA, NVQ, PIE Just for examples and then in Column B i Have say grades like A, B, C, OR D Depending on the grade given.

    I was wondering if there was a sum formula that looks for say the word gcse in column A and then sums only the cellls in column B with say A or B Values.

    I hope this makes some sense let me know if you need some more info.

    JAmidd

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: sum formula to look for text value in column A & then count all cells that have Either

    hi Jamidd. as you know, a sample excel file with the desired answers will always help. since it's grades and not numbers, how do you sum it? i can count how many records GCSE for eg has A values by:
    =COUNTIFS(A:A,"GCSE",B:B,"A")

    you may look at my signature to look at what kind of sample file helps. it'll be good if we can solve the question in a single attempt, rather than back and forth questions.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Thanks for your quick reply

    I have attached a simple table for you to work with.

    That formula would look for all the cells with an A grade but i was wondering if there was one that would look down the cells for both A & B grades or more.

    Thanks

    I really appreciate you help
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Try this:

    =SUM(COUNTIFS(A:A,"GCSE",B:B,{"A","B"}))

  5. #5
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Quote Originally Posted by Root_ View Post
    Try this:

    =SUM(COUNTIFS(A:A,"GCSE",B:B,{"A","B"}))
    Thanks Root, Would this work is the data was i a table? Or does it need to be A:A & B:B
    Last edited by Jamidd1; 09-20-2017 at 11:04 PM.

  6. #6
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Quote Originally Posted by Jamidd1 View Post
    ... Would this work is the data was i a table? Or does it need to be A:A & B:B
    This would work as well:

    =SUM(COUNTIFS(Table1[Item],"GCSE",Table1[Grade],{"A","B"}))

  7. #7
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Hi sorry roots,

    I can not seem to get it to work for some reason, i keep on getting value returned.

    I have attached a new template with the sample of the worksheet i am using may help a lot more!

    Thank you for you help again!
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    483

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Is this what you need? (When posting examples, please include your expected results to minimize the guesswork)

    =SUM(COUNTIFS(Table1[Exam Qualification],"*GCSE*",Table1[Achieved Grade],{"A","B"}))

  9. #9
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2019
    Posts
    270

    Re: sum formula to look for text value in column A & then count all cells that have Either

    Sorry, i will remember that for next time.
    This is quite strange, i used this formula on a mac and it worked fine but on a windows computer it was just coming up with TRUE? Any suggestions?

    Thank you for your help.
    Last edited by Jamidd1; 09-21-2017 at 06:01 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 07-18-2017, 09:42 AM
  2. [SOLVED] Formula needed to only count total cells entered and not count adjacent text entered cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2013, 06:22 PM
  3. [SOLVED] count cells that contain text but do not count cells containing only a formula
    By cprpacific in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-13-2013, 03:15 PM
  4. [SOLVED] Count Cells with text until another column has value
    By philiop in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 04:01 PM
  5. How to count cells in a column that text is red
    By lroyle in forum Excel General
    Replies: 3
    Last Post: 11-10-2009, 11:17 AM
  6. [SOLVED] Count Position of Filtered TEXT cells in a column
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-18-2005, 12:06 AM
  7. [SOLVED] Count Position of Filtered TEXT cells in a column
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-15-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1