Sum If Forumla to some values assocated wtih months of the year

[cartica]

Hi-

Please see attached spreadsheet. I am trying fill in the "Buy", and "Sell" columns in my Turnover tab. It pulls data from the Eze data tab. I want to add to the sumif formula to only add the Month in column A in the Turnover tab with the assocaiated Month data in column B in ezea data. I think the issue is the format the i download the data in. Please help me out.

Thanks

[Richard Buttrey]

Hi,

C2:
=SUMIFS('EZE DATA'!$V$2:$V$5000,'EZE DATA'!$B$2:$B$5000,">="&$A2,'EZE DATA'!$B$2:$B$5000,"<="&EOMONTH($A2,0),'EZE DATA'!$L$2:$L$5000,"BUY")

Copy it to D2 and replace "Buy" with "Sell", then copy C2:D2 down

[cartica]

That formula does not work. If you go into eze data and take october number don't add, also november doesn't even pull in for buys.

[cartica]

It may not be possible because the way the data comes in and I have to do text to columns. some of the dates change to the format example

10/1/13
others come in
1/10/13

So, I guess not possible to know if it means january or october

[Richard Buttrey]

Works OK for me.

See attached where I've already filtered the data for November Buys so that you can see the total in V agrees with the summary table.

Obviously if your data is not consistent then that's a problem with your data and not an Excel problem. Although if you're doing a text to columns you should use the option to define the Date column as a Date rather than General or Text or whatever it might be.

[cartica]

Yes, I define everything as a date-DMY. It needs to be in DMY format for other forumas in my s/s that I didn't provide. In the DMY format it shows most dates in November starting with date/month, however there are days where it has month/date for november. It's the same for the other months. Any way around this, so I can pull in accurate info?

[Richard Buttrey]

Hi,

You'll need to upload your original data file, or at least the Excel file with the single column of values before you perform the TextToColumns. Make a note alongside those dates which are not consistent with the rest so that we can understand what you actually want rather than what the format shows.

[cartica]

find attached. This is what i receive, then I text to columns-date-dmy. You can do text to column to see for yourself. If you start line 947 column B the date is consistant until line 1,045

[benishiryo]

hi cartica. you should be doing a MDY conversion. the deciding factor is the format your data is currently in. how you determine is to look at dates bigger than 12. months are never bigger than 12. so you can see some of the data showing numbers bigger than 12 in the middle. hence, it should be MDY. if you have to do a Text to columns every now & then, maybe this would help:

Please Login or Register  to view this content.

SUMPRODUCT calculates slower though. this is assuming the date is in A2 like your first file & "Buy" in C1. otherwise, you can do a text to column & use Richard's formula:

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I was hoping that the file you sent would be a SINGLE column of text BEFORE you convert it with TextToColumns. The file you've attached has either already been converted or that's what you actually received.

Either way I don't see any differences in in the column B date layouts so am not sure why you think some of them are wrong.

The problem with this is that the 'dates' are not true date numbers which Excel can use for date arithmetic. They are merely strings of data which is why ben's string slicing formula is necessary in order to coerce the strings into something Excel can use.

If you can import or convert the numbers to proper date numbers then normal SUMIFS will work. Are you able to upload the original file which I'm presuming is a .txt file?