+ Reply to Thread
Results 1 to 11 of 11

Sum If Problem

  1. 07-16-2016, 06:01 AM #1
    MarkPoyser
    MarkPoyser is offline
    Registered User
    Join Date
    07-16-2016
    Location
    Nottingham,England
    MS-Off Ver
    2010
    Posts
    4

    Sum If Problem

    I have a particular problem I am trying to solve. I have a number of cells containing alpha/numeric data. I am trying to sum all of the numbers in the range where a particular condition is met. Example;

    Columns A_____B______C
    1______PT/5___PT/10___FT/5
    2
    3

    I need to sum the numbers in the range A1:C1 where the cell contains PT

    I tried using the sumif function in conjunction with the right function but to no avail.

    Could someone please help

    Many Thanks

    Mark
    Last edited by MarkPoyser; 07-16-2016 at 06:03 AM.
    Reply With QuoteRegister To Reply
  2. 07-16-2016, 06:05 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,812

    Re: Sum If Problem

    Try this:

    =COUNTIF(A1:C3,"PT*")

    EDIT: Sorry - I misread the OP, so this won't work!
    Last edited by AliGW; 07-16-2016 at 06:09 AM.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-16-2016, 06:09 AM #3
    MarkPoyser
    MarkPoyser is offline
    Registered User
    Join Date
    07-16-2016
    Location
    Nottingham,England
    MS-Off Ver
    2010
    Posts
    4

    Re: Sum If Problem

    Quote Originally Posted by AliGW View Post
    Try this:

    =COUNTIF(A1:C3,"PT*")
    Hi AliGW

    I ahve tried that, but it just returns the number of instances of "PT" rather the sum of the numbers in the range

    thanks
    Reply With QuoteRegister To Reply
  4. 07-16-2016, 06:11 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,812

    Re: Sum If Problem

    Yes, I know - I realised afterwards that I had misread the OP - sorry.

    Why does the data have to be presented in that way? Is it possible to separate the letters and numbers into two columns?
    Reply With QuoteRegister To Reply
  5. 07-16-2016, 06:20 AM #5
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sum If Problem

    Hi
    Try to use this array formula (enter with CTRL+SHIFT+ENTER)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 07-16-2016, 06:24 AM #6
    MarkPoyser
    MarkPoyser is offline
    Registered User
    Join Date
    07-16-2016
    Location
    Nottingham,England
    MS-Off Ver
    2010
    Posts
    4

    Re: Sum If Problem

    Perfect Jose, thank you very much
    Reply With QuoteRegister To Reply
  7. 07-16-2016, 06:30 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,812

    Re: Sum If Problem

    Mark - thanks for the rep! I'm sorry my hasty answer was of no use. José's answer has gone straight into my Excel notebook for future use!!!
    Reply With QuoteRegister To Reply
  8. 07-16-2016, 06:31 AM #8
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Sum If Problem

    You're welcome.
    Please, don't forget mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  9. 07-16-2016, 08:49 AM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum If Problem

    Here's another one...

    =SUMPRODUCT(--(LEFT(A1:C1,2)="PT"),--MID(A1:C1,4,10))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  10. 07-16-2016, 10:05 AM #10
    MarkPoyser
    MarkPoyser is offline
    Registered User
    Join Date
    07-16-2016
    Location
    Nottingham,England
    MS-Off Ver
    2010
    Posts
    4
    Quote Originally Posted by Tony Valko View Post
    Here's another one...

    =SUMPRODUCT(--(LEFT(A1:C1,2)="PT"),--MID(A1:C1,4,10))
    Great. Thanks Tony.
    Reply With QuoteRegister To Reply
  11. 07-16-2016, 10:53 AM #11
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sum If Problem

    You're welcome!
    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)

Similar Threads

  1. [SOLVED] Excel Macro - Copy & Paste (Font) problem & Delete Last Added Rows problem
    By LennartB in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-05-2015, 06:58 AM
  2. Over thinking an Excel problem - Can I used Nested IF for this problem?
    By bdg724 in forum Excel General
    Replies: 1
    Last Post: 04-01-2015, 02:30 PM
  3. Excel Problem with worksheet, need to know which function for penny a day doubled problem
    By excellhobo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-20-2013, 07:16 PM
  4. input box problem, cell filtering problem, result display while locking sheet
    By croozin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2013, 08:52 AM
  5. Index / Match / Vlookup type problem - £20 to person who solves this! X ref problem
    By aceparts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  6. Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
    By Emsky in forum Excel General
    Replies: 2
    Last Post: 01-22-2013, 07:09 AM
  7. Problem using formulas within macro + problem with defined name.
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2011, 04:39 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1