Count cells in table with specific criteria

drom

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

👍
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
one way, copy table and delete all the ax by replace all with space then you can use countif() for the new table
 

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
686
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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:

drom

Active Member
Joined
Mar 20, 2005
Messages
445
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
How about
=SUMPRODUCT((RIGHT(A2:A6,2)="ax")*(SUBSTITUTE(A2:A6,"ax","")+1>1000))
 

drom

Active Member
Joined
Mar 20, 2005
Messages
445
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
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))
 

drom

Active Member
Joined
Mar 20, 2005
Messages
445
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Hi but I do not know how to avoid the error I get, Check the image:
 

Attachments

  • qqq.PNG
    qqq.PNG
    33 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
If the table is named aa why are you using aaa in the formula?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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
Top