Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Please help!

  1. #1
    Seeker cprop76's Avatar
    Join Date
    Nov 2020
    Posts
    5
    Articles
    0
    Excel Version
    2010 Microsoft365 Click to run

    Please help!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to count two separate pieces of data which appear in the same excel column. The columns will have a date followed by a quantity so look something like this : 24/11/20 (30). I'm trying to count the two pieces of data separately and wondered if that's possible using formulas or do I need to separate the data into two separate columns? Help is greatly appreciated! TIA.

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    283
    Articles
    0
    Excel Version
    2019
    What is the criteria for counting the quantity. Would be helpful if you provided a sample workbook showing what you have and what your expected results should look like. Thinking you need a Sumifs function, but really would help to see your wkb or may need to use PQ to rearrange data first.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,835
    Articles
    0
    Excel Version
    O365
    This array formula will add up the sums

    =SUM(IF(A2:A20<>"",(--LEFT(A2:A20,8)=--"2020-11-24")*SUBSTITUTE(MID(A2:A20,11,99),")","")))

    Might give you a starts.

  4. #4
    Seeker cprop76's Avatar
    Join Date
    Nov 2020
    Posts
    5
    Articles
    0
    Excel Version
    2010 Microsoft365 Click to run

    Thanks, sample attached!

    Quote Originally Posted by alansidman View Post
    What is the criteria for counting the quantity. Would be helpful if you provided a sample workbook showing what you have and what your expected results should look like. Thinking you need a Sumifs function, but really would help to see your wkb or may need to use PQ to rearrange data first.
    Thanks, sorry for the VERY late reply, I've been out of the office all week. I've attached a sample which hopefully makes sense! The left hand side is the sample data and the RHS how i expect it to be presented (or similar). Sample Data.xlsxTIA

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    283
    Articles
    0
    Excel Version
    2019
    A Power Query solution:

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
        #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Split Column by Delimiter", {{"Value.2", each Text.BetweenDelimiters(_, "(", ")"), type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Extracted Text Between Delimiters",{"Value.3"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value.2", Int64.Type}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Attribute"}, {{"Attendees", each List.Sum([Value.2]), type nullable number}, {"Course Count", each Table.RowCount(_), Int64.Type}})
    in
        #"Grouped Rows"
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    1
    Attribute Attendees Course Count
    2
    Online 101
    410
    9
    3
    F2F Midaz
    15
    1
    4
    ZOOM 101
    30
    1
    5
    ZOOM AEM 1&2
    30
    1
    6
    Online AEM P1
    10
    1
    7
    ZOOM AEM Part 2
    5
    1
    Sheet: Sheet1
    Attached Files Attached Files

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,843
    Articles
    0
    Excel Version
    365
    Aagh. Beaten to it!
    Power Query solution at cell H10.
    Update the data in the table at cell A1 then right-click the result table and choose refresh.
    Currently I've hard-coded the date for the current financial year column and I haven't sorted the rows in the same order you have, but if this kind of solution is the way you want to go, I can tweak.
    Click image for larger version. 

Name:	2020-12-01_102333.png 
Views:	7 
Size:	4.7 KB 
ID:	10220
    Attached Files Attached Files
    Last edited by p45cal; 2020-12-01 at 11:29 AM.

  7. #7
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,735
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @cprop
    Hi
    in the future please a more descriptive thread title instead of " please help".
    Thanks
    Thank you Ken for this secure forum.

  8. #8
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,835
    Articles
    0
    Excel Version
    O365
    If you do want formulae., these do it

    J2: =COUNTA(A$2:A$22)
    J3: =COUNTA(B$2:B$22)
    etc.

    K2: =SUM(IF(A$2:A$22<>"",--SUBSTITUTE(MID(A$2:A$22,FIND("(",A$2:A$22)+1,99),")","")))
    K3: =SUM(IF(B$2:B$22<>"",--SUBSTITUTE(MID(B$2:B$22,FIND("(",B$2:B$22)+1,99),")","")))
    ETC.

    K2:K7 are array formulae.

  9. #9
    Seeker cprop76's Avatar
    Join Date
    Nov 2020
    Posts
    5
    Articles
    0
    Excel Version
    2010 Microsoft365 Click to run
    OMG, you're right! That's amazing, thank you so much!

  10. #10
    Seeker cprop76's Avatar
    Join Date
    Nov 2020
    Posts
    5
    Articles
    0
    Excel Version
    2010 Microsoft365 Click to run
    yes you're right, not very helpful! My bad. Thanks

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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