help with sumproductd & date conditions

[rmtaylor]

Can anyone tell why this formula does not work
I am attempting to have a sumproducts formula count a sheet with 4 conditions ( that part is working fine) In addition I would very much like it to count when the four conditions are matched plus 3 month date conditions to be matched say April May & June (allowing me to get results over 4 quarters when it is fully operational

=SUMPRODUCT((((((SHEET1!$A$2:$A$2000="SOUTH")*(SHEET1!$J$2:$J$2000=1)*(SHEET1!$K$2:$K$2000="AVAILABLE")*(SHEET1!$I$2:$I$2000="PAID")*(MONTH(SHEET1!$H$2:$H$2000)=4)+(MONTH(SHEET1!$H$2:$H$2000)=5)+(MONTH(SHEET1!$H$2:$H$2000)=6))))))

Thanks for any replies
Robert

 

[Ian Mac]

On 2002-10-29 02:24, rmtaylor wrote:
Can anyone tell why this formula does not work
I am attempting to have a sumproducts formula count a sheet with 4 conditions ( that part is working fine) In addition I would very much like it to count when the four conditions are matched plus 3 month date conditions to be matched say April May & June (allowing me to get results over 4 quarters when it is fully operational

=SUMPRODUCT((((((SHEET1!$A$2:$A$2000="SOUTH")*(SHEET1!$J$2:$J$2000=1)*(SHEET1!$K$2:$K$2000="AVAILABLE")*(SHEET1!$I$2:$I$2000="PAID")*(MONTH(SHEET1!$H$2:$H$2000)=4)+(MONTH(SHEET1!$H$2:$H$2000)=5)+(MONTH(SHEET1!$H$2:$H$2000)=6))))))

Thanks for any replies
Robert

Your formula work fine for me, although can be shortened to:

=SUMPRODUCT((((((Sheet1!$A$2:$A$2000="SOUTH")*(Sheet1!$J$2:$J$2000=1)*(Sheet1!$K$2:$K$2000="AVAILABLE")*(Sheet1!$I$2:$I$2000="PAID")*(MONTH(Sheet1!$H$2:$H$2000)={4,5,6}))))))

could you tell us what errors you are getting.

here's what I set up from your eg:

Book1
	A	B	C	D	E	F	G
2	south	04/05/2002	paid	1	available		14
3	south	05/05/2002	paid	1	available
4	south	06/05/2002	paid	1	available
5	south	07/05/2002	paid	1	available
6	south	08/06/2002	paid	1	available
7	south	09/06/2002	paid	1	available
8	south	10/06/2002	paid	1	available
9	south	11/06/2002	paid	1	available
10	south	12/06/2002	paid	1	available
11	south	13/05/2002	paid	1	available
12	south	14/05/2002	paid	1	available
13	south	15/05/2002	paid	1	available
14	south	16/05/2002	paid	1	available
15	south	17/05/2002	paid	1	available
Sheet1