Dealing with blank cells in a range

M

manuelphilippe

New Member
Joined
Dec 29, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
I am having an issue. I'm getting a VALUE error. Let me fill you in on the details.

- I am summarizing some budgetary info. I'm using SUMIFS to calculate the sum of my amount column and putting the result in another sheet.

- The sums are being filtered by month based on date.

- The issue I'm having is when the date cell is blank, my formula produces a #VALUE! error if it detects blank cells.

- I have a header on row 1. I'd like to calculate my formula using the entire column except for row 1 as the user may enter data that currently isn't in the sheet. I'd like to have them be able to do that without having to always update the formula.

How should I go about this? If more info is needed, please let me know.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Welcome to the Board!

This is a situation where it would be good to see what your data looks like (especially the situation that produces the #VALUE error), and to see your expected result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
I have two sheets. This one with data:
Personal Finance Tracker Copy.xlsx
ABCDEFG
1DateVendor/SourceAccountAmountTypeCategorySub-Category
2Jan. 01, 2022Vendor ABank$525.00ExpensePersonalGroceries
3Jan. 09, 2022Vendor ABank$15.00ExpensePersonalGroceries
4Jan. 22, 2022Vendor ABank$35.00ExpensePersonalGroceries
5Feb. 02, 2022Vendor ABank$60.00ExpensePersonalGroceries
6Feb. 12, 2022Vendor ABank$45.00ExpensePersonalGroceries
7Mar. 04, 2022Vendor ABank$40.00ExpensePersonalGroceries
8Mar. 26, 2022Vendor ABank$95.00ExpensePersonalGroceries
9Apr. 05, 2022Vendor ABank$90.00ExpensePersonalGroceries
10Apr. 17, 2022Vendor ABank$525.00ExpensePersonalGroceries
11Apr. 26, 2022Vendor ABank$15.00ExpensePersonalGroceries
12May. 03, 2022Vendor ABank$35.00ExpensePersonalGroceries
13May. 09, 2022Vendor ABank$60.00ExpensePersonalGroceries
14May. 15, 2022Vendor ABank$45.00ExpensePersonalGroceries
15May. 31, 2022Vendor ABank$40.00ExpensePersonalGroceries
16Jun. 06, 2022Vendor ABank$95.00ExpensePersonalGroceries
17Jun. 11, 2022Vendor ABank$90.00ExpensePersonalGroceries
18Jun. 23, 2022Vendor ABank$525.00ExpensePersonalGroceries
19Jul. 04, 2022Vendor ABank$15.00ExpensePersonalGroceries
20Jul. 08, 2022Vendor ABank$35.00ExpensePersonalGroceries
21Jul. 15, 2022Vendor ABank$60.00ExpensePersonalGroceries
22Jul. 21, 2022Vendor ABank$45.00ExpensePersonalGroceries
23Jul. 28, 2022Vendor ABank$40.00ExpensePersonalGroceries
24Aug. 09, 2022Vendor ABank$95.00ExpensePersonalGroceries
25Aug. 13, 2022Vendor ABank$90.00ExpensePersonalGroceries
26Aug. 19, 2022Vendor ABank$525.00ExpensePersonalGroceries
27Aug. 26, 2022Vendor ABank$15.00ExpensePersonalGroceries
28Sep. 01, 2022Vendor ABank$35.00ExpensePersonalGroceries
29Sep. 06, 2022Vendor ABank$60.00ExpensePersonalGroceries
30Sep. 11, 2022Vendor ABank$45.00ExpensePersonalGroceries
31Sep. 18, 2022Vendor ABank$40.00ExpensePersonalGroceries
32Sep. 22, 2022Vendor ABank$95.00ExpensePersonalGroceries
33Oct. 01, 2022Vendor ABank$90.00ExpensePersonalGroceries
34Oct. 05, 2022Vendor ABank$525.00ExpensePersonalGroceries
35Oct. 08, 2022Vendor ABank$15.00ExpensePersonalGroceries
36Oct. 13, 2022Vendor ABank$35.00ExpensePersonalGroceries
37Oct. 30, 2022Vendor ABank$60.00ExpensePersonalGroceries
38Nov. 01, 2022Vendor ABank$45.00ExpensePersonalGroceries
39Nov. 10, 2022Vendor ABank$40.00ExpensePersonalGroceries
40Nov. 15, 2022Vendor ABank$95.00ExpensePersonalGroceries
41Nov. 19, 2022Vendor ABank$90.00ExpensePersonalGroceries
42Nov. 28, 2022Vendor ABank$525.00ExpensePersonalGroceries
43Dec. 01, 2022Vendor ABank$15.00ExpensePersonalGroceries
44Dec. 03, 2022Vendor ABank$35.00ExpensePersonalGroceries
45Dec. 04, 2022Vendor ABank$60.00ExpensePersonalGroceries
46Dec. 17, 2022Vendor ABank$45.00ExpensePersonalGroceries
47Dec. 25, 2022Vendor ABank$40.00ExpensePersonalGroceries
48Dec. 31, 2022Vendor ABank$95.00ExpensePersonalGroceries
Data
Cells with Data Validation
CellAllowCriteria
C2:C48ListPayPal, Credit Card, Bank
E2:E1047636ListExpense, Income
F2:F1047636ListBusiness, Personal


and this which summarizes the data:
Personal Finance Tracker Copy.xlsx
ABCDEFGHIJKLM
1MonthJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
2Groceries$575.00$105.00$135.00$630.00$180.00$710.00$195.00$725.00$275.00$635.00$795.00$290.00
2022 Monthly Analysis
Cell Formulas
RangeFormula
B2B2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&B1,Data!A2:A48,"<="&EOMONTH(B1,0),Data!G2:G48,"Groceries")
C2C2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&C1,Data!A2:A48,"<="&EOMONTH(C1,0),Data!G2:G48,"Groceries")
D2D2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&D1,Data!A2:A48,"<="&EOMONTH(D1,0),Data!G2:G48,"Groceries")
E2E2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&E1,Data!A2:A48,"<="&EOMONTH(E1,0),Data!G2:G48,"Groceries")
F2F2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&F1,Data!A2:A48,"<="&EOMONTH(F1,0),Data!G2:G48,"Groceries")
G2G2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&G1,Data!A2:A48,"<="&EOMONTH(G1,0),Data!G2:G48,"Groceries")
H2H2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&H1,Data!A2:A48,"<="&EOMONTH(H1,0),Data!G2:G48,"Groceries")
I2I2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&I1,Data!A2:A48,"<="&EOMONTH(I1,0),Data!G2:G48,"Groceries")
J2J2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&J1,Data!A2:A48,"<="&EOMONTH(J1,0),Data!G2:G48,"Groceries")
K2K2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&K1,Data!A2:A48,"<="&EOMONTH(K1,0),Data!G2:G48,"Groceries")
L2L2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&L1,Data!A2:A48,"<="&EOMONTH(L1,0),Data!G2:G48,"Groceries")
M2M2=SUMIFS(Data!D2:D48,Data!A2:A48,">="&M1,Data!A2:A48,"<="&EOMONTH(M1,0),Data!G2:G48,"Groceries")


I am having an issue. I'm getting a VALUE error. Let me fill you in on the details.

- I am summarizing some budgetary info. I'm using SUMIFS to calculate the sum of my amount column and putting the result in another sheet.

- The sums are being filtered by month based on date.

- The issue I'm having is when the date cell is blank, my formula produces a #VALUE! error if it detects blank cells.

- I have a header on row 1. I'd like to calculate my formula using the entire column except for row 1 as the user may enter data that currently isn't in the sheet. I'd like to have them be able to do that without having to always update the formula.

How should I go about this? If more info is needed, please let me know.
 
Upvote 0
manuelphilippe said:
- The issue I'm having is when the date cell is blank, my formula produces a #VALUE! error if it detects blank cells.
Click to expand...
Which cell is blank?
I do not see any errors in your example.

If I remove a date from column A on the Data sheet, all the formulas still work for me on the Summary sheet without error.
Can you show me a situation resulting in this error?
 
Upvote 0
I should have explained better.

But, anyhow, it seems that I solved the problem of getting it to calculate the entire column by using A:A.
 
Upvote 0
manuelphilippe said:
But, anyhow, it seems that I solved the problem of getting it to calculate the entire column by using A:A.
Click to expand...
Yes, that would answer your second question, but what about the first with the #VALUE error?
I don't think changing the cell reference will fix that.

If you can show us an example of this error, we can probably help you fix it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
170
RemyMoo
R
L
  • Question
XIRR CALCULATION WITH BLANK CELLS
Replies
1
Views
618
StephenCrump
S
J
  • Question
Power Query - Column that Concatenates a column and a text version of a date.
Replies
2
Views
82
jaihawk8
J
M
  • Question
Formula to populate the next available cell in a column using data populated in another cell.
Replies
4
Views
311
Marshkevuk
M
afrazier1221
  • Solved
VBA adding symbols, disabling the intended code. Please help.
Replies
1
Views
254
afrazier1221
afrazier1221

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

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