Formula needed for adding

G

Graywolf80

New Member
Joined
May 11, 2010
Messages
20
Hello,

I need to see if there is a way for a cell to add up the number of rows that have been entered. If I have a total of 20 rows of information and only have 14 of those rows that have information entered, how can i get the cell to input the #14 stating that 14 items have been entered, no matter what the value of cells are.

Example

Row 1 $10
Row 2 $50
Row 3 $100
Row 4 $25
Row 5 $50

Cell that needs info should say [5] Entries

Also,

If 1 column has information input like a counting system, and the count moves up or down, is there a formula that can add the number of times the count moved.

Example:

Count 0
Count 1
Count 4
Count 2
Count 2
Count 2
Count -1

In this example the count moved a total of 4 times. When the count remained [2] for 3 hands in a row, I do not want the cell to add those counts since it did not move.

Thanks in advance
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Question 1
Data in column A:
=COUNTA(A1:A10)



Question 2.
Data in column A
Array formula(confirm CTRL+ShIFT+ENTER)
=SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
 
Upvote 0
Thank you on Question 2.

On Question 1. What if all the cells have a value to it but only want to count the values over a certain $. So I dont want to include any $0 values. Instead of deleting those cells.
 
Upvote 0
Graywolf80 said:
Hello,

I need to see if there is a way for a cell to add up the number of rows that have been entered. If I have a total of 20 rows of information and only have 14 of those rows that have information entered, how can i get the cell to input the #14 stating that 14 items have been entered, no matter what the value of cells are.

Example

Row 1 $10
Row 2 $50
Row 3 $100
Row 4 $25
Row 5 $50

Cell that needs info should say [5] Entries

Also,

If 1 column has information input like a counting system, and the count moves up or down, is there a formula that can add the number of times the count moved.

Example:

Count 0
Count 1
Count 4
Count 2
Count 2
Count 2
Count -1

In this example the count moved a total of 4 times. When the count remained [2] for 3 hands in a row, I do not want the cell to add those counts since it did not move.

Thanks in advance
Click to expand...
Try these...

Question 1:

=COUNTA(A1:A20)

Question 2:

=SUM(--(FREQUENCY(A1:A20,A1:A20)>0))-(COUNT(A1:A20)>0)
 
Upvote 0
Back to Question 2. I tried both formulas and it seems that after a few scenarios, it doesnt always show how many times the count moved.
 
Upvote 0
Graywolf80 said:
Back to Question 2. I tried both formulas and it seems that after a few scenarios, it doesnt always show how many times the count moved.
Click to expand...
Works for me. Here's my interpretation.

Book1
AB
10_
211
341
421
520
620
7-11
Sheet1

The count changed from 0 to 1, from 1 to 4, from 4 to 2 and finally, from 2 to -1.
 
Upvote 0
Here is the column I am trying to do:
<TABLE style="WIDTH: 44pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=59 border=0><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 id=td_post_2746492 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=59 height=20>0</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>0</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>-1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>-1</TD></TR></TBODY></TABLE>
With your forumla, the only thing i changes was the Column Letter and the range for it to check. If you add it manually, it should be 11 changes, the cell says only 6 with the forumla. Forumla using =SUM(--(FREQUENCY(G7:G46,G7:G46)>0))-(COUNT(G7:G46)>1)
 
Upvote 0
Graywolf80 said:
Here is the column I am trying to do:
<TABLE style="WIDTH: 44pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=59 border=0><COLGROUP><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 id=td_post_2746492 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 44pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=59 height=20>0</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>0</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>-1</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>-1</TD></TR></TBODY></TABLE>
With your forumla, the only thing i changes was the Column Letter and the range for it to check. If you add it manually, it should be 11 changes, the cell says only 6 with the forumla. Forumla using =SUM(--(FREQUENCY(G7:G46,G7:G46)>0))-(COUNT(G7:G46)>1)
Click to expand...
Ok, now I see...

Try this...

=SUMPRODUCT(--(G8:G23<>G7:G22))

Note how the ranges are offset.
 
Upvote 0
Thank you. I had to add a -1 at the end to ensure it wasnt counting the remaining cells that are not part of the end result. Because after the data is done, there is usually left over cells that have a 0 in it. But it works with every scenario
 
Upvote 0
Graywolf80 said:
Thank you. I had to add a -1 at the end to ensure it wasnt counting the remaining cells that are not part of the end result. Because after the data is done, there is usually left over cells that have a 0 in it. But it works with every scenario
Click to expand...
Good deal. Thanks for the feedback! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Need help! extract value in special case, find many method, cannot solve it!
Replies
6
Views
176
CLKinfo
C
A
  • Solved
COUNT multiple SUM COUNTIFS
Replies
6
Views
337
Peter_SSs
Peter_SSs
M
  • Question Question
CountIfs Help
Replies
9
Views
131
Joe4
Joe4
JTL9161
  • Question Question
Need to cells with no data but with a formula
Replies
5
Views
97
Peter_SSs
Peter_SSs
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
140
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,574
Messages
6,179,628
Members
452,933
Latest member
patv

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