Add only values that contain "d" and in the same month

Hi all,

I am going mad trying to resolve this!

I have dates in column A, and the ability to enter a reference of either "d" or "w" in column C. In column D a 'number of times' can be enter. So for example:

Col A Col C Col D
01/01/2011 d 5
01/01/2011 d 6
01/01/2011 w 9
02/01/2011 d 4
10/02/2011 d 4
11/02/2011 w 5
11/02/2011 d 5
11/02/2011 w 9

I want to return in Column E that there have been 15 "ds" in January, 9 "ws" in January, 9 "ds" in February, and 14 "ws" in February.

Any help will be greatly appreciated!

Hi,

=SUMPRODUCT((MONTH(A1:A8)=1)*(C1:C8="d")*(D1:D8))

will return the total d's for January.

Adjust MONTH(A1:A8)=1 to MONTH(A1:A8)=2 for February.

XL2007 onwards you should really be using SUMIFS rather than SUMPRODUCT.

Perhaps you could post a sample file to illustrate your requirements, how you wish to incorporate "year" and indeed how "year" is to be determined

In your sample be sure to outline expected results.

Once we have a better idea of your requirements we may also look to retitle your thread.

If you put a date in AN22, 1st of the month that you want to calculate, then you could use this version

=SUMPRODUCT((MONTH($A$15:$A$10000)=MONTH(AN22))*(YEAR($A$15:$A$10000)=YEAR(AN22))*($C$15:$C$10000="d"),$D$15:$D$10000)

or as Donkeyote says, you can use SUMIFS, i.e.

=SUMIFS($D$15:$D$10000,$A$15:$A$10000,">="&AN22,$A$15:$A$10000,"<="&EOMONTH(AN22,0),$C$15:$C$10000, "d")

Adapting the SUMIFS previously provided:

Please Login or Register  to view this content.

Assuming the dates are to increment:

Please Login or Register  to view this content.

Originally Posted by Excel_Monkey

Sorry, just encountered a further problem.

When the date rolls over to next jan (month 13) this is not recognised by excel?

So, you need it to be year specific? Add another term to the sumproduct....

=SUMPRODUCT((YEAR(A1:A8)=2011)*(MONTH(A1:A8)=1)*(C1:C8="d")*(D1:D8))

The MONTH() function returns a number between 1 and 12 that represents the month portion of the date. It won't return 13.

Sorry, I can't make this work. See adapted sheet.

Originally Posted by Excel_Monkey

Sorry, I can't make this work. See adapted sheet.

Perhaps try the suggested formula ... ie N$14 not N$16 (you want to reference D/W dynamically)

@Excel_Monkey

Please do not create duplicates of the same question - as you can see this leads to confusion and potentially wastes peoples time (freely donated)

I've merged the threads but as you can see some posts now appear out of sequence.

Stick to one thread going forward - if you need to clarify requirements do so by replying to your original thread (only)