+ Reply to Thread
Results 1 to 13 of 13

Counta of a column G if column B = matching text

  1. 04-30-2012, 05:43 PM #1
    Aisling
    Aisling is offline
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    14

    Counta of a column G if column B = matching text

    I need to obtain a COUNTA of column G if column B = matching text

    B G

    GSM 20
    HAA 10
    GSM 10
    HAA 20

    I need a cell for GSM to = 2

    I do not need to sum column G just count the instances.
    I have tried COUNTIFS but it is coming up with too few calculations
    Reply With QuoteRegister To Reply
  2. 04-30-2012, 06:25 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,941

    Re: Counta of a column G if column B = matching text

    The solution *is* COUNTIF or COUNTIFS. If you're not getting the answer you expect, check the data (F2 to edit the cells) and see if there are any trailing spaces.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 05-01-2012, 01:53 AM #3
    mmmarks
    mmmarks is offline
    Forum Contributor
    Join Date
    11-26-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    159

    Re: Counta of a column G if column B = matching text

    HI,

    why didnt you try for Countif function ???

    =countif(range,"Criteria")
    eg. countif(A1:B4,"Gsm")
    Reply With QuoteRegister To Reply
  4. 05-01-2012, 05:30 AM #4
    Aisling
    Aisling is offline
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Counta of a column G if column B = matching text

    Thanks
    I have tried =COUNTIF($G$20:$G$73:$B$20:$B$73,"GSM") but it is counting column B. I want it to count column G if column B contains GSM
    Not sure what I am doing wrong have tried lots of variations like =COUNTIFS($G$20:$G$73,$B$20:$B$73,"GSM") but none working.

    Any ideas would be GREATLY appreciated :-)
    Reply With QuoteRegister To Reply
  5. 05-01-2012, 05:34 AM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counta of a column G if column B = matching text

    Hi

    As you are in Excel 2003, try this.

    =SUMPRODUCT((B20:B73="GSM")*(G20:G73))

    Is it OK for you?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  6. 05-01-2012, 05:40 AM #6
    Aisling
    Aisling is offline
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Counta of a column G if column B = matching text

    Thanks Fotis

    Nearly there :-) it is adding the monetary amounts together in the column, I need it to count just the number of times it appears.
    Have tried counta but not working.

    Regards
    Aisling
    Reply With QuoteRegister To Reply
  7. 05-01-2012, 05:46 AM #7
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counta of a column G if column B = matching text

    =COUNTIF(B20:B75,"GSM")

    If this is not, also, works for you, pls, upload a small sample workbook.
    Reply With QuoteRegister To Reply
  8. 05-01-2012, 06:05 AM #8
    Aisling
    Aisling is offline
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Counta of a column G if column B = matching text

    I am trying to get highlighted column L to count the number of cells with information by column B information.
    Column M is working fine so I just need a similar formula to count rather than add the information.

    Sample file.xlsx
    Reply With QuoteRegister To Reply
  9. 05-01-2012, 06:23 AM #9
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counta of a column G if column B = matching text

    In L3>>>=COUNTIF($B$20:$B$75,"GSM")

    In L4>>>=COUNTIF($B$20:$B$75,"HAA")

    ..And so on.....
    Reply With QuoteRegister To Reply
  10. 05-01-2012, 06:28 AM #10
    Aisling
    Aisling is offline
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Counta of a column G if column B = matching text

    Hi Fotis

    Thank you for the above but this counts column B I need it to count column G when it gets paid back.

    Thanks
    Reply With QuoteRegister To Reply
  11. 05-01-2012, 06:33 AM #11
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counta of a column G if column B = matching text

    Another suggestion..

    L3>>=SUMPRODUCT(($B$20:$B$75="GSM")*($G$20:$G$75<>""))

    L4>> =SUMPRODUCT(($B$20:$B$75="HAA")*($G$20:$G$75<>""))
    Reply With QuoteRegister To Reply
  12. 05-01-2012, 06:42 AM #12
    Aisling
    Aisling is offline
    Registered User
    Join Date
    02-23-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Counta of a column G if column B = matching text

    Perfect
    Thank you so much Fotis

    Regards
    Aisling
    Reply With QuoteRegister To Reply
  13. 05-01-2012, 06:45 AM #13
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Counta of a column G if column B = matching text

    You are welcome!

    Target was to understant what you want. When i did....

    Pls. mark your Thread, as Solved.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1