Count value monthly

[Yvonne Ng]

Hi,
how do I calculate a Colomn of values in Coloumn B with respected of individual month (dd-mm--yy) in column A?


please advise.

 

[Yvonne Ng]

Hi, Thank you for yr reply.
As per yr post, you've mention that :
1-Jan-13 (perhaps custom formatted as: Jan-13)
But because of the requirement, I need the date to be include as part of the data too instead of consist of only month and year. ��

 

[helpexce]

Try this.

Jan	1
Feb	2
Mar	3
Apr	4
May	5
Jun	6
Jul	7
Aug	8
Sep	9
Oct	10
Nov	11
Dec	12

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

=SUMPRODUCT(--($A$2:$A$8=E2),--(MONTH($B$2:$B$8)=VLOOKUP(F2,$K$1:$L$12,2,0)),$C$2:$C$8)

 

[Aladin Akyurek]

Hi, Thank you for yr reply.
As per yr post, you've mention that :
1-Jan-13 (perhaps custom formatted as: Jan-13)
But because of the requirement, I need the date to be include as part of the data too instead of consist of only month and year. &#55357;&#56842;

Your exhibit shows full dates. If that's the case: see

https://dl.dropboxusercontent.com/u/65698317/ConditionalSum%20Yvonne%20Ng.xlsx

 

[Yvonne Ng]

May I know where to get the data for F2 for the vlookup?

 

[Yvonne Ng]

Yes, the table shown exacts what I need.
do i have to include the date shown in F1 or i can actually just indicate the month, such as jan, feb, etc thought my data given in coloum b is with date-month-year
?

 

[helpexce]

May I know where to get the data for F2 for the vlookup?

Sorry I forgot to mention that. It looks up the month, Jan-Dec for which you want the sum.

 

[helpexce]

You have to make the separate table as in one of my previous post.
Br,

 

[Yvonne Ng]

i need look up on monthly basis.
example
in jan, beta total amount
delta total amount
in feb, beta total amount
delta total amount
etc..

 

[Yvonne Ng]

can the formular works on different worksheet? as i try to input into another worrksheet, but the total amount given is 0.

 

[helpexce]

i need look up on monthly basis.
example
in jan, beta total amount
delta total amount
in feb, beta total amount
delta total amount
etc..

Month	No. Rep.	Beta	Delta		Group	DATE	Amount
Jan	1	11	0		Beta	01.Jan.2013	$4.00
Feb	2	4	4		Beta	01.Feb.2013	$4.00
Mar	3	0	0		Beta	02.Jan.2013	$3.00
Apr	4	0	0		Delta	03.May.2013	$4.00
May	5	0	4		Delta	11.Jun.2013	$5.00
Jun	6	0	5		Beta	01.Jan.2013	$4.00
Jul	7	0	0		Delta	01.Feb.2013	$4.00
Aug	8	0	0
Sep	9	0	0
Oct	10	0	0
Nov	11	0	0
Dec	12	0	0

<colgroup><col span="6"><col><col></colgroup><tbody>
</tbody>

MY DATA IS STARTING FROM A1:

IN CELL WHICH HAS ANSWER 11(ONE CELL BELOW BETA) I HAVE FORMULA=SUMPRODUCT(--($F$2:$F$8=$C$1),--(MONTH($G$2:$G$8)=$B2),$H$2:$H$8)
IN CELL WHICH HAS ANSWER 0(ONE CELL BELOW DELTA)=SUMPRODUCT(--($F$2:$F$8=$D$1),--(MONTH($G$2:$G$8)=$B2),$H$2:$H$8)

BOTH FORMULA COPIED DOWN.