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

 

[Ardus Petus]

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

HTH
--
AP

"businesslady24"
<[email protected]> a écrit dans
le message de (e-mail address removed)...

 

[Franz Verga]

Nel post *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)

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

"businesslady24"

 

[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 poste
here. I forgot to mention the orginal formula i posted had a typo, bu
i have been chanign that when i try to use any of the formula (K2
should have read K420

 

[businesslady24]

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

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

thank yo

 

[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, bu
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 (e-mail address removed)...

 

[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)

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

--
HTH

Bob Phillips

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

"businesslady24"
message news:[email protected]...