Count cells in table with specific criteria

[drom]

Hi and thanks in advance!


I have a table named Table1 with many rows and columns

The DataBodyRange of this table contains always values such as

• 45658ax
• 12ax
• 9878ax
• 887412ax
• 557ax

So they have always a string "ax" at the end of the cell

I would like to count how many of those cells have a value > 1000

In my eg ig If have only the above cells...

The result should be = 3

 

[AlanY]

one way, copy table and delete all the ax by replace all with space then you can use countif() for the new table

 

[JimM]

Add another column to extract the numbers

eg assuming data starts in A2

=--LEFT(A2,LEN(A2)-2)

Then use COUNTIF to count those over 1000

=COUNTIF(B2:B6,">1000")

 

[sandy666]

at first look with Power Query
(not optimised)

Column1	Column2	Column3	Column4		ax > 1000
9231	2062	5304	487		3
6399	7681	1569	887412ax
2132	5494	1343	7464
930	4666	12ax	5333
5923	45658ax	3283	6156
7607	5273	6949	2572
1185	6196	6269	9838
1757	1947	9041	557ax
9878ax	6160	1367	1952
7742	4885	9718	5092

let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    TFL = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandListColumn(TFL, "Column1"),
    Type = Table.TransformColumnTypes(Expand,{{"Column1", type text}}),
    FilterAX = Table.SelectRows(Type, each Text.EndsWith([Column1], "ax")),
    TBD = Table.TransformColumns(FilterAX, {{"Column1", each Text.BeforeDelimiter(_, "ax"), type text}}),
    Number = Table.TransformColumnTypes(TBD,{{"Column1", Int64.Type}}),
    Filter = Table.SelectRows(Number, each [Column1] > 1000),
    Count = Table.RowCount(Filter)
in
    Count

number of columns - doesn't matter
source will stay as is

 

[drom]

ADVERTISEMENT

Hi!
I cant replace the ax because Comes from a ERP and I need then to export them (I could but its easier avoid this option)
I cant add other columns because my table has many Columns (today 20, tomorrow 30, and the day after tomorrow who knows)
I do not want to use VBA (I know how to ..., or power query, same reason)
I would like to use a Formula and not a UDF
I guess a solution is a sumproduct, but I do not know how to...

 

[Fluff]

How about
=SUMPRODUCT((RIGHT(A2:A6,2)="ax")*(SUBSTITUTE(A2:A6,"ax","")+1>1000))

 

[drom]

ADVERTISEMENT

How about
=SUMPRODUCT((RIGHT(A2:A6,2)="ax")*(SUBSTITUTE(A2:A6,"ax","")+1>1000))

Hi Works perfect if every cell comes with ax at the end, and they should come this way but:
But In case a cell does not have ax, because the cell is blank, ?
is is possible to avoid these cells #VALUE! ??

 

[Fluff]

If the numbers are always whole numbers & you are happy with >=1000, then try

=SUMPRODUCT((RIGHT(A2:A6,2)="ax")*(LEN(SUBSTITUTE(A2:A6,"ax",""))>3))

 

[drom]

Hi but I do not know how to avoid the error I get, Check the image:

 

[Fluff]

If the table is named aa why are you using aaa in the formula?