Sumproduct based on multple conditions

[Shamsuddeen]

Hi all!

I have a table with the following data:

Column A-Date Column B-Amount

01-Jan-2000 25000
12-Jan-2000 15000
18-Jan-2000 10000
20-Mar-2001 2500
25-Mar-2001 3000
01-Aug-2002 45000
02-Aug-2002 100
31-Aug-2003 650

and so on......

I would like to have the totat and count of amount per month as below:

Period Total Count
Jan-2000 xxxxx xx
Mar-2001 xxxxxx xx
Aug-2002 xxxxxx xx
Aug-2003 xxxxxx xx


Thanks for the help in advance.


Shamsuddeen

 

[north19701]

it looks like a pivot table would be one option, and group your period my month and year.

 

[Shamsuddeen]

Thanks for the reply.

My table contains more than 1000 rows and I need to preserve the original table as is and the total and count should appear at the bottom of the table with the sumproduct formula.



Any help please.


Shamsuddeen

 

[Aladin Akyurek]

Thanks for the reply.

My table contains more than 1000 rows and I need to preserve the original table as is and the total and count should appear at the bottom of the table with the sumproduct formula.



Any help please.


Shamsuddeen

I don't think that's a good reason, but lets have it as you wish...

=SUMPRODUCT(--(TEXT(DateRange,"m/yy")=TEXT(CritDate,"m/yy"),AmountRange)

which gives you the total wrt the month/year in CritDate.

=SUMPRODUCT(--(TEXT(DateRange,"m/yy")=TEXT(CritDate,"m/yy"),--ISNUMBER(AmountRange))

counts the amounts wrt CritDate.

 

[north19701]

Book8
	A	B	C	D	E	F	G	H
1	01-Jan-2000	25000
2	12-Jan-2000	15000
3	18-Jan-2000	10000
4	20-Mar-2001	2500
5	25-Mar-2001	3000
6	01-Aug-2002	45000
7	02-Aug-2002	100
8	31-Aug-2003	650
9
10
11		Total	Count
12	Jan-00	50000	3
13	Mar-01	5500	2
14	Aug-02	45100	2
15	Aug-03	650	1
16
17
18
19
20
Sheet3

Something like this?

the formula in b12 is:
=SUMPRODUCT((MONTH($A$1:$A$8)=MONTH($A12))*(YEAR($A$1:$A$8)=YEAR($A12)),$B$1:$B$8)

and c12 is:

=SUMPRODUCT((MONTH($A$1:$A$8)=MONTH($A12))*(YEAR($A$1:$A$8)=YEAR($A12)))

I really believe the pivot table would be much easier to manage, though.

 

[Shamsuddeen]

Thanks for all the replies.

I will give a try.



Shamsuddeen