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

1. 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!

Sorry sheet attached for clarity.

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.

Thanks,

Exactly what I wanted. It appears I really have a lot to learn!

Sorry, just encountered a further problem.

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

6. Month 13 (i.e. 12+1,2,3 etc.)

I am using the follow formula kindly provided by Sweep.

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

In this formula AN22 contains the month reference (2 in this case for Feb), AN23 would contain 3 for March. The problem is that AN33 contains number 13 and excel doesn't like it. I somehow need to include the year ref in this but don't know how. Any ideas?

7. Re: Month 13 (i.e. 12+1,2,3 etc.)

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.

8. Re: Month 13 (i.e. 12+1,2,3 etc.)

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")

10. Re: Month 13 (i.e. 12+1,2,3 etc.)

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

12. Re: Month 13 (i.e. 12+1,2,3 etc.)

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

13. Re: Month 13 (i.e. 12+1,2,3 etc.)

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.

15. Re: Add only values that contain "d" and in the same month

My Apologies - I am new on here.

There are currently 1 users browsing this thread. (0 members and 1 guests)