Sumif by date

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140
Hi,

I'm attempting to use the sumif command depending on a date in column D. D is formatted as a date (mmm-yy).

Column V contains the numbers i need to add.

If the dates in D is january and the year is 06, then i want the corresponding numbers in column V added.

What would the formula for this be?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Hi

I put todays date in D1 and put 12 in V1

In any column i put this:

=SUM(D1+V1)

It returned 08/08/07

Hope this helps
 

Hozz

Board Regular
Joined
Feb 10, 2005
Messages
140
No. You misunderstand.

I have a column of random numbers in V.

If the date in column D is showing as january 2006 then i want the corresponding numbers in column V to be added together.
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
=SUMIF(H8:H97,"<"&"01-feb-07",I8:I97)-SUMIF(H8:H97,"<"&"01-jan-07",I8:I97)
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
sumif is normally used to add data in one column under some crietria.;
if the adding depends upon value in another column better use sumproduct.

see the sample sheet below
see formula in B12 . modify formula to suit you.
Book1
ABCD
11-Jul10
22-Jul4
33-Jul6
44-Jul7
51-Jul6
62-Jul8
71-Jul9
82-Jul5
91-Jul7
10
11
1232
Sheet1
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
So....you have a date in D1, D2 D3 etc....
and you want them adding to V1, V2, V3??


My original post would work:

=SUM(D1+V1) fill this down whichever column you are going to have the formula would then produce
=SUM(D+V2) etc etc
 
Joined
Jul 30, 2006
Messages
3,656
Hozz,

Try this (you may have to adjust the ranges):
=SUMPRODUCT(--(TEXT($D$2:$D$1000,"mmm-yy")="Jan-06"),$V$2:$V$1000)

Have a great day,
Stan
 

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
Stan, why do you feel the need to coerce the date into text?

I am asking you this because, Venkat provided an example above and it works perfect without coercing the date.
 

Forum statistics

Threads
1,181,658
Messages
5,931,269
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top