Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

**peculiar101** · 11-21-2018, 01:15 PM

Hi all,

I am working a spreadsheet for my job. I want to count the number of cells in a given area that contain hard entered numbers <=50, however within the same range are cells that sum up a column of data. I want to be able to exclude those cells from the formula without having to split the range. There are multiple sheets in the same workbook and the location of the calculating cells changes in each so to change the split range would be kind of a pain.

I've tried a wide range COUNTIF and COUNTIFS and they still count the cells that contain the =SUM() formulas. They either include them in the count or return 0. I tried a SUMPRODUCT formula, but I don't understand the dynamics enough to get it to do what I want.

Essentially I want it to do the following:

=COUNTIF(rng,"<=50") but NOT cells that contain =SUM()

Thanks!

**José Augusto** · 11-21-2018, 01:28 PM

Try this array formula

Formula:

Please Login or Register  to view this content.

**peculiar101** · 11-21-2018, 01:37 PM

=SUMPRODUCT((F6:I12>=50)*(IFERROR(LEFT(FORMULATEXT(F6:I12),4)="=SUM",0)))

It returns 0, it should be 4 if It's counting correctly.

**José Augusto** · 11-21-2018, 01:39 PM

Have you entered the formula with CTRL+Shift+Enter ?

To enter a array formula you must confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**peculiar101** · 11-21-2018, 01:44 PM

Yes, okay, that worked BUT it returned 3 instead of 4....

**Richard Buttrey** · 11-21-2018, 01:54 PM

Hi,

I believe there's an =ISFORMULA() function in Excel 2016 and later.
If you want a UDF then here's one I made earlier

Formula:

Please Login or Register  to view this content.

In the worksheet use

=COUNTFORMULAS(your_range)

See attached example

**José Augusto** · 11-21-2018, 01:58 PM

Originally Posted by peculiar101

Yes, okay, that worked BUT it returned 3 instead of 4....

Can you show me the four values?

**peculiar101** · 11-21-2018, 01:59 PM

The four highlighted cells contain the =SUM() formula calculating the sums of the two rows below them. I want the formula to just count the cells where I've entered 36.

**peculiar101** · 11-21-2018, 02:01 PM

Originally Posted by Richard Buttrey

=COUNTFORMULAS(your_range)

See attached example

This would work for counting the formulas, but I want to count the cells with numbers and exclude the ones with formulas...

**Richard Buttrey** · 11-21-2018, 02:02 PM

One other way
1. Select your range
2 Hit the F5 Goto key and choose Special
3 Select Formulas & OK

You'll see the Count of the formula in the bar at the bottom

**peculiar101** · 11-21-2018, 02:07 PM

Originally Posted by Richard Buttrey

You'll see the Count of the formula in the bar at the bottom

Thanks, but I don't want to count the formulas...

**José Augusto** · 11-21-2018, 02:14 PM

My mistake
try
=SUMPRODUCT((rng<=50)*(IFERROR(LEFT(FORMULATEXT(rng),4)="=SUM",0)))

**peculiar101** · 11-21-2018, 02:24 PM

Oh, I'd fixed that. Still calculating 3... (frustrating isn't it? lol)
Now it's just the principle of the thing! There has to be a way...

**José Augusto** · 11-21-2018, 02:34 PM

Show me the formula you have to get the value 72.

**peculiar101** · 11-21-2018, 02:37 PM

=sum(g11:g12)

**José Augusto** · 11-21-2018, 02:42 PM

Ok

Last try, I suppose

=SUMPRODUCT((rng<=50)*(IFERROR(LEFT(FORMULATEXT(rng),4)<>"=SUM",0)))

**Richard Buttrey** · 11-21-2018, 02:54 PM

Originally Posted by peculiar101

Thanks, but I don't want to count the formulas...

Hi, sorry should have said select constants. However I missed the <= 50 bit.

Here's the adjusted UDF

Please Login or Register  to view this content.

**peculiar101** · 11-21-2018, 05:48 PM

Originally Posted by José Augusto

Ok

Last try, I suppose

=SUMPRODUCT((rng<=50)*(IFERROR(LEFT(FORMULATEXT(rng),4)<>"=SUM",0)))

Returns 0 :/

**peculiar101** · 11-21-2018, 05:54 PM

Originally Posted by Richard Buttrey

Hi, sorry should have said select constants. However I missed the <= 50 bit.

Here's the adjusted UDF

Please Login or Register  to view this content.

That works!!! Thank you!

Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

LinkBack

Thread Tools

Rate This Thread

Display

Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Re: Formula to count number of cells containing entered numbers but NOT a cell with =SUM()

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Altering formula to add cell numbers rather than count the cells

[SOLVED] need a total count of number of times a value is entered in a cell

I need to count the number of cells that have alphanumeric characters entered

[SOLVED] Formula needed to only count total cells entered and not count adjacent text entered cells

[SOLVED] LEN Function, count number of Numbers in a cell, but count if are 12 numbers the most..

Creating a number entry cell to list entered numbers?

numbers being entered show in formula bar but not in cell?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions