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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Joe4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
68,115
Office Version
  1. 365
Platform
  1. Windows
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
M

manuelphilippe

New Member
Joined
Dec 29, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
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
Joe4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
68,115
Office Version
  1. 365
Platform
  1. Windows
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
M

manuelphilippe

New Member
Joined
Dec 29, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. MacOS
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
Joe4

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
68,115
Office Version
  1. 365
Platform
  1. Windows
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
Importing Data Causing Blank Cells to be Non-Blank
Replies
7
Views
312
Fluff
Fluff
E
  • Question
Tracking Change
Replies
0
Views
57
excelesha
E
J
  • Solved
How to sum values in column D until a blank cell is reached and if values in column C equals "WP" or "BS"
Replies
9
Views
171
Peter_SSs
Peter_SSs
J
  • Question
Trendlines - ignoring zero/blank values in the range
Replies
3
Views
132
awoohaw
awoohaw
J
  • Question
Add blank row only applying to active worksheet
Replies
2
Views
121
Jaye16
J

Forum statistics

Threads
1,195,990
Messages
6,012,723
Members
441,722
Latest member
tpaman1975

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
Top