Formula help for rolling month to date

[akvpv6]

I am hoping someone can help me with my formula to show rolling month to date data in a table. The problem with my current formula is that in the months with no data it is just carrying over the total from the previous month as you can see in the image attached.

I want the cells from Jul - Mar to show as empty for Month to Date until data is entered in Quoted Provided. The formula I used is IF(H3<>"",SUM($H$3:H3),"")

It is also important to note I am looking to keep a rolling balance so July MTD would be July, June, May, and April data summed.

Thank you!

 

[etaf]

IF(H3<>"",SUM($H$3:H3),"")

so whats in K3 - as if blank then the formula should work - as it will be
IF(K3<>"",SUM($H$3:K3),"")
and so as K3 is blank, then this formula should return blank

maybe a hidden character in the cell ??? K3

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[akvpv6]

IF(H3<>"",SUM($H$3:H3),"")

so whats in K3 - as if blank then the formula should work - as it will be
IF(K3<>"",SUM($H$3:K3),"")
and so as K3 is blank, then this formula should return blank

maybe a hidden character in the cell ??? K3

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

IF(H3<>"",SUM($H$3:H3),"")

so whats in K3 - as if blank then the formula should work - as it will be
IF(K3<>"",SUM($H$3:K3),"")
and so as K3 is blank, then this formula should return blank

maybe a hidden character in the cell ??? K3

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thank you for the response, at the moment the best I can offer is an additional picture of my workbook. As you can see I have a table that monthly data is manually entered. In the table to the right, I have a formula =IFERROR(SUMIF($A:$A,H2,$C:$C),"") that pulls the monthly data, for months with no quotes provided yet the formula is pulling in a "0" which I hid for aesthetics. I believe because the formula is bringing in a "0" the MTD formula =IF(H3<>"",SUM($H$3:H3),"") is taking that as a sign to roll the data over from the previous balance.


I filled out a few more rows of data for this response. For example, since I have no quotes for December (O3) I want there to be no numbers in (O7).

 

[etaf]

I believe because the formula is bringing in a "0" the MTD formula =IF(H3<>"",SUM($H$3:H3),"") is taking that as a sign to roll the data over from the previous balance.

yes because its not blank

=IFERROR(SUMIF($A:$A,H2,$C:$C),"")

Not sure i follow that - as it wont be a blank - it will just sum based on whatever is in H2
and return a zero , if nothing to SUM, unlikely to be an error - as far as excel is concerned

whats the chances of returning a valid 0 and wanting to sum ?

IF(K3<>0,SUM($H$3:K3),"")

 

[akvpv6]

yes because its not blank


Not sure i follow that - as it wont be a blank - it will just sum based on whatever is in H2
and return a zero , if nothing to SUM, unlikely to be an error - as far as excel is concerned

whats the chances of returning a valid 0 and wanting to sum ?

IF(K3<>0,SUM($H$3:K3),"")

IF(K3<>0,SUM($H$3:K3),"") worked!

I didn't even think that I should be using the 0 to my advantage in the formula and not trying to treat it as a blank

thank you!!

 

[etaf]

you are welcome