Ignore blanks in calculation

[Brutus32]

Hello!

I just joined this forum and I hope I get many new tips and tricks to use Excel, but for now I have a problem.

I have massive data with precipitation (daily), which I need to calculate into decades of precipitation, however my different forms of formulas won't seem to ignore blanks so far, but instead I get zeros as result (which is not correct). The thing is that I get blanks, if I put condition, when value is greater than zero and I get zeros instead of blank, when I use formula, where result may be equal or greater than zero. What I want is that I could include actual zeros in calculation, but exclude blanks from calculation, so that my results actually give me zeros and blanks where needed.

Formula last used: =IF(SUMIFS(H$4:H$7455; $B$4:$B$7455; D7458; $C$4:$C$7455; C7458; H$4:H$7455; ""); ""; SUMIFS(H$4:H$7455; $B$4:$B$7455; D7458; $C$4:$C$7455; C7458))

Legend:
H column is one of the datasets
B column is month-year (like june-2014)
C column is decade (like I decade, II decade, III decade, as in sets of 10-11 days)
D7458 is one of the months used as sumif condition
C7458 is one of the decades used as sumif condition

[FDibbins]

If you want to include 0 but exclude blanks, you need to test for =0 and <>""

I am not sure which criteria ranges you need to test this for

[Brutus32]

Well, the tricky part is that (which so far I haven't got) I need to have sum of zero, where the sum is actually zero and sum of blank, where there is nothing to sum up. I got the blanks, but that also made all zeros into blanks, but some zeros are actual zeros, not blanks.

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Pepe Le Mokko]

Try playing around with the ISNUMBER function which will count 0's and the ISBLANK function which will count empty cells ( it will NOT count cells containing a null text string like "")

[Brutus32]

Here is dummy file. I put two months of random numbers (lets just say that those are daily precipitation data). What I need:

1) Sum for all decades (e.g. June 1st decade, June 2nd decade, etc) separate so that it includes all values (including negatives, since I have discovered some in the actual data and negative rainfall might be a thing, but exclude all blanks in a manner that the sum also would be blank.
2) Sum for monthly data as well in same fashion as for decades data.

[Glenn Kennedy]

Is this what you mean???

In K2:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$62)/(COUNTIFS($K$1:$K1,$B$2:$B$62,$L$1:$L1,$C$2:$C$62)=0),1)),"")

In L2:
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW($B$2:$B$62)/(COUNTIFS($K$1:$K1,$B$2:$B$62,$L$1:$L1,$C$2:$C$62)=0),1)),"")

In M2:
=IF(K2="","",SUMPRODUCT(($B$2:$B$62=K2)*($C$2:$C$62=L2)*$E$2:$I$62))

with a similar formula for monthyl totals.

[Brutus32]

Not quite. The formula might work (?), but I also need that very same data by stations as separate units. Forgot to mention this earlier.

[Glenn Kennedy]

An insignificant detail (!!!???).

[Brutus32]

"Totally" insignificant. I can see the frustation in those question marks and exclamations. However, thank you for the help. One more thing to ask:

I made some changes to dummy file, what I need that sums of zeros are actually zeros, but blank sums remain blanks

[Glenn Kennedy]

Not sure about that. I'll need to let that ferment in my head for a while.

[Glenn Kennedy]

In M2, copied across and down:
=IFERROR(IF(SUMPRODUCT(--(LEN(INDEX(E:E,AGGREGATE(15,6,ROW(E$2:E$62)/(($B$2:$B$62=$K2)*($C$2:$C$62=$L2)),1)):INDEX(E:E,AGGREGATE(14,6,ROW(E$2:E$62)/(($B$2:$B$62=$K2)*($C$2:$C$62=$L2)),1)))>0))=0,"",SUMPRODUCT(($B$2:$B$62=$K2)*($C$2:$C$62=$L2)*E$2:E$62)),"")

In T2, copied across and down:
=IFERROR(IF(SUMPRODUCT(--(LEN(INDEX(E:E,AGGREGATE(15,6,ROW(E$2:E$62)/($D$2:$D$62=$S2),1)):INDEX(E:E,AGGREGATE(14,6,ROW(E$2:E$62)/($D$2:$D$62=$S2),1)))>0))=0,"",SUMPRODUCT(($D$2:$D$62=$S2)*E$2:E$62)),"")

[Brutus32]

I would spread you more rep, if I could, because that is awesome! Thank you!

[Pepe Le Mokko]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Glenn Kennedy]

Brutus32, one final word. The above solution wil work ONLY if the data are sorted, as per your example, with all values (whether real or null) for each month/decade are adjacent to each other. If they are NOT, you will need to sort them frst to bring them adjacent.