using SUMIF for Date entries?

[businesslady24]

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

[SteveG]

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

[Franz Verga]

Nel post news:[email protected]
*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

[Bob Phillips]

=SUMPRODUCT(--(TEXT(I5:I20,"mmmm")="June"),K5:K20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"businesslady24"
<[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ardus Petus]

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

HTH
--
AP

"businesslady24"
<[email protected]> a écrit dans
le message de news:
businesslady24.29t23c_1150982402.518...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
>

[businesslady24]

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]

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]

I have sorted it, silly mistake, the correct formula was:

=sumif(I5:I420,"*june*",K5:K420)

thank you

[businesslady24]

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?

[Ardus Petus]

As previously proposed:
=SUMPRODUCT(--(MONTH(I5:I420)=6),K5:K420)

Cheers,
--
AP


"businesslady24"
<[email protected]> a écrit dans
le message de news:
businesslady24.29t6ho_1150988102.394...rum-nospam.com...
>
> 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
>

[businesslady24]

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.

[Bob Phillips]

Are they real dates or text dates?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =SUMPRODUCT(--(TEXT(I5:I20,"mmmm")="June"),K5:K20)
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "businesslady24"
> <[email protected]> wrote in
> message
news:[email protected]...
> >
> > 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
> >
>
>