Count cells in table with specific criteria

drom

Well-known Member
Joined
Mar 20, 2005
Messages
521
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
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

?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
one way, copy table and delete all the ax by replace all with space then you can use countif() for the new table
 
Upvote 0
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")
 
Upvote 0
at first look with Power Query
(not optimised)
Column1Column2Column3Column4ax > 1000
9231206253044873
639976811569887412ax
2132549413437464
930466612ax5333
592345658ax32836156
7607527369492572
1185619662699838
175719479041557ax
9878ax616013671952
7742488597185092

Power Query:
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
 
Last edited:
Upvote 0
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...
 
Upvote 0
How about
=SUMPRODUCT((RIGHT(A2:A6,2)="ax")*(SUBSTITUTE(A2:A6,"ax","")+1>1000))
 
Upvote 0
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! ??
 
Upvote 0
If the numbers are always whole numbers & you are happy with >=1000, then try
Excel Formula:
=SUMPRODUCT((RIGHT(A2:A6,2)="ax")*(LEN(SUBSTITUTE(A2:A6,"ax",""))>3))
 
Upvote 0
Hi but I do not know how to avoid the error I get, Check the image:
 

Attachments

  • qqq.PNG
    qqq.PNG
    33 KB · Views: 4
Upvote 0
If the table is named aa why are you using aaa in the formula?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top