Issues w/sums counting "0" as "1"

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
Office Version
  1. 365
Platform
  1. Windows
I'm trying to create a way so that new values are listed in a separate column.

If the value of FA(X) is equal to nothing, then nothing, otherwise display the number that corresponds to where it sits in the list.

In FB, if the value does equal to where it sits, then the count starts there (with a value of "1"). As we go down, the next would be a value of "2".
The formula in FB11 is: =IF(FA11<>E11,FB$7,SUM(FB$7:FB10+1)).

In the example shown, the value that SHOULD be in FB11 is "1", not "4". It's counting the "0's" as "1".
Subsequently, the next that SHOULD have a value is FB14 with a value of "2", not "11".
The formula in FB14 is: =IF(FA14<>E14,FB$7,SUM(FB$7:FB13+1)).

What I'm trying to do is get a column (which will likely be FC) that has listing/count from the small(k-th) of the new-found values, 1- however many there ends up being.

1617980471741.png


Why is this counting the "0's" as ones???
Thank you.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Perhaps what you meant was
Excel Formula:
=SUM(FB$7:FB10)+1

Currently your formula is set to add 1 to every value, and then SUM the result.
 
Upvote 0
What it SHOULD be doing is if the value in the column prior equals nothing, then 0. Otherwise take the value above it and add one.
 
Upvote 0
If you're just taking the value above, why are you using SUM at all?

In your current formula,
Excel Formula:
FB$7:FB10+1
means add 1 to each value in FB7:FB10, so you get 0+1,0+1,0+1,0+1 and then those four are summed to give you 4. If you use SUM(FB$7:FB10)+1, you'd get the sum of FB7:FB10, which is 0, then add 1 to give you 1.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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