count formula

K

khalil

Board Regular
Joined
Jun 2, 2011
Messages
100
hello all

i have formulas in cell a2:a10 , there are no values yet
a1 has the count function , a1 always displays the number of cells including the formulas with no values,
how can i get red of this, i want in cell a1 just the count of cell including values not formulas...?

thanks
:)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
khalil said:
hello all

i have formulas in cell a2:a10 , there are no values yet
a1 has the count function , a1 always displays the number of cells including the formulas with no values,
how can i get red of this, i want in cell a1 just the count of cell including values not formulas...?

thanks
:)
Click to expand...

You probably need...

=COUNTIF(A2:A10,"?*")

If not, would you post the count formula that you have.
 
Upvote 0
khalil said:
hello all

i have formulas in cell a2:a10 , there are no values yet
a1 has the count function , a1 always displays the number of cells including the formulas with no values,
how can i get red of this, i want in cell a1 just the count of cell including values not formulas...?

thanks
:)
Click to expand...
What kind of data do the formulas return? Text? Numbers? Could be either?

Here's a generic formula:

=SUMPRODUCT(--(A2:A10<>""))
 
Upvote 0
khalil said:
=COUNTA(A10:INDEX(A:A,LOOKUP(2,1/(A2:A10="dual"),ROW(A2:A10))+1))


thanks
Click to expand...
Is that the formula you currently have in cell A1?

If the range is completely empty the formula will return 1. That's probaly not what you want, is it?

Try this:

=IF(COUNTIF(A2:A10,"dual"),COUNTA(A10:INDEX(A2:A10,MATCH("dual",A2:A10,0)+1)),"")

Or, using a cell to hold the criteria:

C1 = dual

=IF(COUNTIF(A2:A10,C1),COUNTA(A10:INDEX(A2:A10,MATCH(C1,A2:A10,0)+1)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counting number of cells with formula returning a value OR a #DIV/0 error?
Replies
3
Views
204
Eric W
Eric W
S
  • Question Question
Look up todays date and special copy paste value for multiple sheets
Replies
4
Views
200
OaklandJim
O
E
  • Question Question
split cell at specific character count but break at a whole word
Replies
6
Views
527
felixstraube
F
A
  • Solved
Counting items by criterias in 2 columns
Replies
4
Views
107
FormR
FormR
R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
116
Domenic
D

Forum statistics

Threads
1,224,522
Messages
6,179,293
Members
452,902
Latest member
Knuddeluff

Share this page

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