Problem with blank cell in finding top n values

toufik3078

New Member
Joined
Apr 25, 2016
Messages
4
I want to find the sum of top 3 values from several datas..But if my datasheet is not complete(that means i have less than two datas) and I want to get sum of the two datas..How can I do that?(if I put 0 on the blank cells,it works.But I dont want to put 0,I need to solve it by having the blank cells
 

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
You should just be able to SUM them even if there is an empty cell. It would only use the 2 Values to SUM. If for some reason you're getting an error when you try to SUM (3) Values but only (2) are available maybe try something like this:

Code:
=IFERROR(SUM(A1:A3),SUM(A1:A2))
Just replace A1:A3 and A1:A2 with the cells you're trying to SUM.
 

toufik3078

New Member
Joined
Apr 25, 2016
Messages
4
You should just be able to SUM them even if there is an empty cell. It would only use the 2 Values to SUM. If for some reason you're getting an error when you try to SUM (3) Values but only (2) are available maybe try something like this:

Code:
=IFERROR(SUM(A1:A3),SUM(A1:A2))
Just replace A1:A3 and A1:A2 with the cells you're trying to SUM.
But for the next to,I need to do B1,B3 as for B2 it shows error..then I have to write a different logic.Is there any way to put just one logic? Thanks by the way:)
 

toufik3078

New Member
Joined
Apr 25, 2016
Messages
4
And I need to show for the result if there is only one value..For two value your solution is okay..but what I do if I need to show when there is only one value?
 

Meggesto

Board Regular
Joined
Mar 8, 2016
Messages
216
So I'm not sure which type of errors you have but if they're all the same then you can use something like this:

Code:
=+SUMIF(A1:A3,"<>#DIV/0")
Where you'd replaced the "#DIV/0!" portion with whichever error message is in the cell if they're consistent.

You could also try:

Code:
=+IF(ISNUMBER(A1),A1,"")
Then Drag this function through from Column A1 to Column Z1 (However far your data goes); Then repeat this from A2 to Z2; and A3 to Z3. It will pull all the number values out of the data and then you can sum the newly created table of values.
 
Last edited:

Forum statistics

Threads
1,081,623
Messages
5,360,083
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top