# Sumif by date

#### Hozz

##### Board Regular
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

Hi
A1=sumif(D1:D100,"jan-06",V1:V100)
Ravi

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.

=SUMIF(H8:H97,"<"&"01-feb-07",I8:I97)-SUMIF(H8:H97,"<"&"01-jan-07",I8:I97)

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

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

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

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.

Replies
2
Views
225
Replies
3
Views
182
Replies
3
Views
231
Replies
5
Views
226
Replies
2
Views
217

1,221,059
Messages
6,157,684
Members
451,433
Latest member
AndyB86

### 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.

### Which adblocker are you using?

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

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