Please help!

cprop76

New member
Joined
Nov 24, 2020
Messages
5
Reaction score
0
Points
0
Excel Version(s)
2010 Microsoft365 Click to run
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.
 
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.
 
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.
 
Thanks, sample attached!

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). View attachment Sample Data.xlsxTIA
 
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
AttributeAttendeesCourse 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
 

Attachments

  • Sample Data.xlsx
    30.4 KB · Views: 12
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.
2020-12-01_102333.png
 

Attachments

  • ExcelGuru10940Sample Data.xlsx
    22.9 KB · Views: 11
Last edited:
@cprop
Hi
in the future please a more descriptive thread title instead of " please help".
Thanks
 
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.
 
OMG, you're right! That's amazing, thank you so much!
 
yes you're right, not very helpful! My bad. Thanks
 
Thanks so much for your reply, finally got this sorted now. Thank you!
 
Back
Top