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.

 

[Aladin Akyurek]

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
?

You can just put say Jan in F1...

Group	DATE	Amount			Jan
Beta	1-Jan-13	$4.00		Beta	11
Beta	1-Feb-13	$4.00		Delta	0
Beta	2-Jan-13	$3.00
Delta	3-May-13	$4.00
Delta	11-Jun-13	$5.00
Beta	1-Jan-13	$4.00
Delta	1-Feb-13	$4.00

<TBODY>
</TBODY>

F2, copied down:

=SUMPRODUCT($C$2:$C$8,--($A$2:$A$8=$E2),--($B$2:$B$8-DAY($B$2:$B$8)+1=("1-"&F$1)+0))

This treats Jan F1 as a month of the current year.

 

[Yvonne Ng]

I've try with the formula by :
=SUMPRODUCT(Internal!K5:K10,--(Internal!G5:G15=$A6),--(Internal!N5:N10-DAY(Internal!N5:N10)+1=("1-"&B$5)+0))
Whereby K5 : K10 is the colomn for amount
G5 : G15 is my list if group, beta, delta
A6 = Delta
N5 : N10 is my date : 1-Jan-2013 (for example)
and
B5 = Jan

But shown me #value as my answer

 

[Aladin Akyurek]

I've try with the formula by :
=SUMPRODUCT(Internal!K5:K10,--(Internal!G5:G15=$A6),--(Internal!N5:N10-DAY(Internal!N5:N10)+1=("1-"&B$5)+0))
Whereby K5 : K10 is the colomn for amount
G5 : G15 is my list if group, beta, delta
A6 = Delta
N5 : N10 is my date : 1-Jan-2013 (for example)
and
B5 = Jan

But shown me #value as my answer

N5:N10 must be true dates;
G5:G10 must house group values like Delta, etc.
K5:K10 must house numbers we can sum.

A6: Delta
B5: Jan

In B6 enter:

=SUMPRODUCT(Internal!$K$5:$K$10,--(Internal!$G$5:$G$10=$A6),--(Internal!N5:N10-DAY(Internal!$N$5:$N$10)+1=("1-"&B$5)+0))

should work as intended.

 

[Yvonne Ng]

It's working finally. Thank you so much

 

[Aladin Akyurek]

It's working finally. Thank you so much

You are welcome. Thanks for providing feedback.