# Date ranges in excel.

Pusherman 21:08 04 Jun 2004
Locked

In Excel (Excel 97 if that makes a difference) I have two columns; Col A contains dates (not necessarily in date order) and col B contains values. I want to add all values that fall in a particular month.

For example, I want to be able to say 'Total all values in col B where the corresponding date in col A is June-04'.

I have this formula which will total all values after the date I specify but that isn't quite what I want.

{=SUM((a1:a10>=VALUE("1 June 2004"))*B1:B10)}

Thanks to all

Pusherman.

Simsy 23:22 04 Jun 2004

the SUMIF function...

If just made it work, but it depends on how, exactly, you are specifying the month you want to refer to...

The basic syntax is;

=SUMIF(RANGE TO CHECK, VALUE TO LOOK FOR, RANGE TO ADD UP)

It's the middle bit you need to be careful about defining.

Good luck,

post back if you still need help, with more detail of how you determine which month to look for.

Regards,

Simsy

Pusherman 23:50 04 Jun 2004

The sumif function would be great if I was looking for a particular date;

=sumif(a1:a10,"01-Jun-04",b1:b10)

But I want to add up all entries for June-04 regardless of the specific date. What I can't do with sumif is say;

=sumif(a1:a10,"Jun-04",b1:b10)

because that will only add entries for June 1st.

Anymore ideas...

Simsy 06:10 05 Jun 2004

if you go via another cell you can...

1)name another cell, "CHECK"

2)make the formula above =sumif(a1:a10,CHECK,b1:b10)

3)Enter into the CHECK cell Jun-04

and you will find this works. You can adapt this "CHECK to contain all the entries in range A1:A10

I hope this helps,

Regards,

Simsy

Pusherman 12:44 05 Jun 2004

Yes that would work. I could have an extra column with 104 for Jan-04 and 204 for Feb-04 and then do a sumif on 104.

I do find it a bit strange that there is not a function to do this automatically. Adding up one column based on a date range in another strikes me as being quite a basic thing to want to do.

If anyone has any other ideas please post them.

Thanks,
Pusherman.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment rĂ©initialiser votre PC, ordinateur portable ou tablette Windows ?