|
using SUMIF for Date entries?
Hi I am trying to use sumif to collect date information for me. The spreadsheet is basically a large list of jobs that have been carried out with the date of job. I am trying to get excel to add up the amounts for each job in march, each in april etc. I have this as my spreadsheet Col I____________________ColK 12th june 2006__________£46.24 18th may 2006__________£116.24 the formula i was using was SUMIF=(I5:I420, "june", K5:K20) for june etc. Can anyone see where i am going wrong? I don't think it is picking June up on the search because the cell actually contains the whole date- day, month, year, but these are necessary. Thanks -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
Try, SUMIF=(I5:I20, "*june*", K5:K20) Your original was looking for an exact match for "june" but your cells contain more data than that so use the * wildcard in the search criteria. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
Nel post news:businesslady24.29t23c_1150982402.5185@excelfo rum-nospam.com
*businesslady24* ha scritto: Hi I am trying to use sumif to collect date information for me. The spreadsheet is basically a large list of jobs that have been carried out with the date of job. I am trying to get excel to add up the amounts for each job in march, each in april etc. I have this as my spreadsheet Col I____________________ColK 12th june 2006__________£46.24 18th may 2006__________£116.24 the formula i was using was SUMIF=(I5:I420, "june", K5:K20) for june etc. Can anyone see where i am going wrong? I don't think it is picking June up on the search because the cell actually contains the whole date- day, month, year, but these are necessary. Hi, I think it dependes on how it is the input of the dates: they are text or real dates you can change formatting as you want... However the formula should have been written: =SUMIF(I5:I420, "june", K5:K20) -- Ciao Franz Verga from Italy |
using SUMIF for Date entries?
=SUMPRODUCT(--(TEXT(I5:I20,"mmmm")="June"),K5:K20)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "businesslady24" <businesslady24.29t23c_1150982402.5185@excelforu m-nospam.com wrote in message news:businesslady24.29t23c_1150982402.5185@excelfo rum-nospam.com... Hi I am trying to use sumif to collect date information for me. The spreadsheet is basically a large list of jobs that have been carried out with the date of job. I am trying to get excel to add up the amounts for each job in march, each in april etc. I have this as my spreadsheet Col I____________________ColK 12th june 2006__________£46.24 18th may 2006__________£116.24 the formula i was using was SUMIF=(I5:I420, "june", K5:K20) for june etc. Can anyone see where i am going wrong? I don't think it is picking June up on the search because the cell actually contains the whole date- day, month, year, but these are necessary. Thanks -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
SteveG Wrote: Try, SUMIF=(I5:I20, "*june*", K5:K20) Your original was looking for an exact match for "june" but your cells contain more data than that so use the * wildcard in the search criteria. HTH Steve Hi i tried this and it didn't work :( Any other ideas as to why it may not be working. I'd always thought it would be a simple formula. -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
sorry guys none of these are working! :eek: have tried with+without the wildcard, and all other formulas posted here. I forgot to mention the orginal formula i posted had a typo, but i have been chanign that when i try to use any of the formula (K20 should have read K420) -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
I have sorted it, silly mistake, the correct formula was: =sumif(I5:I420,"*june*",K5:K420) thank you -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
No sorry i was wrong........ (i'm having a bad day) The formula only works if you only have 'june' typed in a cell, but does not work if you have a date such as '12 june 2006' Any ideas? -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
As previously proposed:
=SUMPRODUCT(--(MONTH(I5:I420)=6),K5:K420) Cheers, -- AP "businesslady24" <businesslady24.29t6ho_1150988102.3942@excelforu m-nospam.com a écrit dans le message de news: ... No sorry i was wrong........ (i'm having a bad day) The formula only works if you only have 'june' typed in a cell, but does not work if you have a date such as '12 june 2006' Any ideas? -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
thanks but that one is not working either. When i first put it into the cell its not even recogninsing that it is a function and it stays as text. I've also tried changing the ; between I5 and I420 to : which it accepts as a formula but it is still not working. -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
using SUMIF for Date entries?
Are they real dates or text dates?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(TEXT(I5:I20,"mmmm")="June"),K5:K20) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "businesslady24" <businesslady24.29t23c_1150982402.5185@excelforu m-nospam.com wrote in message news:businesslady24.29t23c_1150982402.5185@excelfo rum-nospam.com... Hi I am trying to use sumif to collect date information for me. The spreadsheet is basically a large list of jobs that have been carried out with the date of job. I am trying to get excel to add up the amounts for each job in march, each in april etc. I have this as my spreadsheet Col I____________________ColK 12th june 2006__________£46.24 18th may 2006__________£116.24 the formula i was using was SUMIF=(I5:I420, "june", K5:K20) for june etc. Can anyone see where i am going wrong? I don't think it is picking June up on the search because the cell actually contains the whole date- day, month, year, but these are necessary. Thanks -- businesslady24 ------------------------------------------------------------------------ businesslady24's Profile: http://www.excelforum.com/member.php...o&userid=35669 View this thread: http://www.excelforum.com/showthread...hreadid=554535 |
| All times are GMT +1. The time now is 12:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com