Date ranges in excel.

  Pusherman 21:08 04 Jun 04
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 04

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 04

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 04

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 04

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.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…