+ Reply to Thread
Results 1 to 6 of 6

Sum of text cells with multiple criteria

  1. #1
    Registered User
    Join Date
    09-06-2014
    Location
    Cape Breton, NS
    MS-Off Ver
    2010
    Posts
    4

    Sum of text cells with multiple criteria

    Hello,

    I am now trying to add the # of Checksheets (Column B) that has the same description with the following criteria for each Subsystem (Column C):

    Column A - 'B Checksheet"
    Broken out by each Checksheet Disc (E,I,M,H).

    Thanks!
    CBrez

    aTracker Test.xlsm

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum of text cells with multiple criteria

    If you want to count all the "E"s, then this will do it for you...
    =COUNTIF(D:D,"E")

    However, if you want to count all "E"s that are, for instance, E-001AQ, then you will need to have list of those as well, and then use the COUNTIFs() function.

    Perhaps if you included a few sample answers, it would help?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-06-2014
    Location
    Cape Breton, NS
    MS-Off Ver
    2010
    Posts
    4

    Re: Sum of text cells with multiple criteria

    On the Trim tab, for Subsystem '863-001' (Column C) and Checksheet Disc 'E' (Column D) and Check Type 'B Check' (column A), there are 52 total checksheets but only 4 diff values. 4 would be the result I'm looking for.

    Thank you!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum of text cells with multiple criteria

    OK try this approaqch using a helper column on Trim sheet, M2, copied down (you can hide this if you want)..
    =IF(COUNTIFS($A$2:A2,A2,$C$2:C2,C2,$D$2:D2,D2,$B$2:B2,B2)>1,"",1)

    Then for the summary...
    =SUMIFS(Trim!$M:$M,Trim!$C:$C,Summary!$B5,Trim!$D:$D,LEFT(Summary!$H$3,1),Trim!$A:$A,LEFT(Summary!$G$2,7))
    copied down.

    You will need to adjust the reference for the headings in row 3 (bolded) for each "type"

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Sum of text cells with multiple criteria

    Quote Originally Posted by Cbrez View Post
    On the Trim tab, for Subsystem '863-001' (Column C) and Checksheet Disc 'E' (Column D) and Check Type 'B Check' (column A), there are 52 total checksheets but only 4 diff values. 4 would be the result I'm looking for.

    Thank you!
    Couldn't understand...
    Explain it..
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Sum of text cells with multiple criteria

    Vikus, the OP is referring to column headings, and the contents of those columns. They want to count unique entries based on those columns and the table on "sheet1"

+ 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. [SOLVED] SumIfs with multiple criteria (search column for cells w/ text containing)
    By jerrydiaz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2013, 01:06 PM
  2. [SOLVED] Formula to return specific text based on multiple cells meeting a single criteria
    By missydanni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2013, 09:48 AM
  3. Replies: 5
    Last Post: 02-04-2013, 05:32 PM
  4. [SOLVED] Using IF to fill cells with TEXT to show staus based on multiple criteria
    By karstens in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2012, 10:23 AM
  5. Replies: 2
    Last Post: 12-03-2011, 03:51 AM

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