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

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

=SUMPRODUCT(--(MONTH(I5;I420)=6);K5:K420)

HTH
--
AP

"businesslady24"
<businesslady24.29t23c_1150982402.5185@excelforu m-nospam.com a écrit dans
le message de news:
...

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

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

=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

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

sorry guys none of these are working!

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

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

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

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

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

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