• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to collate dates with amounts and total by month/year.

Eloise T

Eloise T

Active Member
Using the attached file, I need a formula beginning in Cell C4 (which I can drag to the right) to add the amounts (in Column H), by date (in Column G) and put the totals under the correct month and year listed in C3 through BM3.

Thank you in advance.
 

Attachments

I like helper columns when dates like this are involved... Year*100+Month

Feb XX, 2016 becomes 201602 and now easy to sumif(s) w/201601, 201602, etc...

Can do the same w/YYYYWW for weeks...

See if this helps you...

You can make those helper columns white text on white background if you don't want to see them

Paul
 

Attachments

C4: =SUMIFS($H$7:$H$11794,$G$7:$G$11794,"<="&EOMONTH(C3,0),$G$7:$G$11794,">="&C3) copy across

Please note that there are no dates in cells:
G1250, G2383, G2384, G2542, G2799,

There are lots of cells in Column G with N/A also
 
PaulF said:
I like helper columns when dates like this are involved... Year*100+Month

Feb XX, 2016 becomes 201602 and now easy to sumif(s) w/201601, 201602, etc...

Can do the same w/YYYYWW for weeks...

See if this helps you...

You can make those helper columns white text on white background if you don't want to see them

Paul
Click to expand...
This just one caveat. There are cells in the date column which have N/A. They need to "adopt" either the upper or lower date as their own so they can be added into the mix.
 
Hui said:
C4: =SUMIFS($H$7:$H$11794,$G$7:$G$11794,"<="&EOMONTH(C3,0),$G$7:$G$11794,">="&C3) copy across

Please note that there are no dates in cells:
G1250, G2383, G2384, G2542, G2799,

There are lots of cells in Column G with N/A also
Click to expand...
I just noticed that. Ouch! SO.....The N/A cells need to "adopt" either the upper or lower date as their own so they can be added into the mix. The same with the cells with no date. They need to grab an adjacent date so the amount isn't lost. If both G and H are blank, they can be ignored. It couldn't be simple, could it? Assuming your formula works (which I have no reason to doubt), I can plug N/A into the blank dates if that helps?
 
Last edited:
For the N/A I would add a helper column now and use that to do the calculations above

eg: F7: =IF(G7="N/A",G8,G7)
Copy down

Then in C4:
=SUMIFS($H$7:$H$11794,$F$7:$F$11794,"<="&EOMONTH(C3,0),$F$7:$F$11794,">="&C3) copy across
 
Hui said:
If you want to allow for the blank cells you could also do that

eg: F7: =IF(or(G7="N/A", G7=""),G8,G7)
Copy down
Click to expand...

As it turns out the "blanks" were not really blanks, but white on white, so I changed them to black on white. This whole messed is something I inherited from the previous person who's gone and am expected to make sense of it all....not to mention I keep adding more rows from data sent to me each week. I've been trying to control the formatting to keep it uniform, but sometimes "stuff" slips through.

Speaking of stuff slipping through...While manually checking, I just noticed there are several "N/A" that are multiples in a row. Can =IF(G7="N/A",G8,G7) be modified to handle that?
Maybe =IF(G7 is a number (date) then G7 else if not check upwards until you find a number?)

Thank you so much for your help...and PaulF too if you're reading this.
 
Last edited:
Hui said:
If you want to allow for the blank cells you could also do that

eg: F7: =IF(or(G7="N/A", G7=""),G8,G7)
Copy down
Click to expand...
Found another anomaly, but this doesn't necessarily need fixed.
Note in Rows 10976 through 10995, et. al., there's a date vs. a number.
What's really strange is that if you put your left click on cell G10976 (and following, individually) then click to the right of the data in the "entry" line at the top of the screen which is just to the right of the Fx, and then hit [Enter], the date goes away in column F and the 5-digit number appears which allows for correct addition.
Very strange. I'm sure there's an explanation, but I certainly don't know what it is at the moment. Any ideas? Please see attachment.
 

Attachments

Last edited:
select F7:F11794
Goto Format Cells (Ctrl+1)
Number Format
Custom Format
Select a format that suits or type dd mmm yy etc
 
Thanks! Leave it to a Ninja to know the best way. :DD

Any thoughts on serial cells with "N/A" ?

BTW, changing the format didn't work.
 
Hui said:
select F7:F11794
Goto Format Cells (Ctrl+1)
Number Format
Custom Format
Select a format that suits or type dd mmm yy etc
Click to expand...
I noticed there was one segment of the spreadsheet that had 32 N/As in a row.
I'll probably deal with that by hand but I tried to expand your formula for dealing with N/A and it didn't fly. It is as follows:
=IF(G7="N/A",G8,IF(G8="N/A",G9,IF(G9="N/A",G10,IF(G10="N/A",G11,IF(G11="N/A",G12,IF(G12="N/A",G13,IF(G13="N/A",G14,IF(G14="N/A",G15,IF(G15="N/A",G16,G7)))))))))

Essentially it just keeps looking until it doesn't find N/A up to 9 or 10 tries.

I've got to be close, any suggestions?
 
Change the original formula to make a N/A cell equal to the cell above it
 
Hui said:
Change the original formula to make a N/A cell equal to the cell above it
Click to expand...
This worked:
=IF(G7<>"N/A",G7,
IF(G8<>"N/A",G8,
IF(G9<>"N/A",G9,
IF(G10<>"N/A",G10,
IF(G11<>"N/A",G11,
IF(G12<>"N/A",G12,
IF(G13<>"N/A",G13,
IF(G14<>"N/A",G14,
IF(G15<>"N/A",G15,G16)))))))))

Which takes care of all the multiple N/As except for the one that's 32x long.
 
You must log in or register to reply here.
Back
Top