Please help!

[cprop76]

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.

[alansidman]

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.

[Bob Phillips]

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.

[cprop76]

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

[alansidman]

A Power Query solution:

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

[p45cal]

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.

[Pecoflyer]

@cprop
Hi
in the future please a more descriptive thread title instead of " please help".
Thanks

[Bob Phillips]

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.

[cprop76]

OMG, you're right! That's amazing, thank you so much!

[cprop76]

yes you're right, not very helpful! My bad. Thanks