need a 'ProductIf' solution

[FrankExcel]

don't know why excel didn't do productIF just like it did for sumIF...

if I have dates and use the eomonth() function to grab the end of date like this

7/31/09 7/23/09
7/31/09 7/22/09
7/31/09 7/21/09
6/30/09 6/29/09
6/30/09 6/28/09
6/30/09 6/27/09
etc...


now I want to do the product of all the numbers in the next column over for the month of July.

if this were 'sumif' -- it would be sumif(range, criteria, [range to sum])

what I would like is productif(range, criteria, [range to calculate product])

is there an easy way to do this in a single cell?

 

[Peter_SSs]

If you have dates covering more than one year, then you may need a bit more, but SUMPRODUCT is the function you are after.

Note that you could do this without the end of month formula if you want - just use column B in my formula instead of column A.

Excel Workbook
	A	B	C	D	E
1	31/07/2009		1		6
2	31/07/2009		2
3	31/07/2009		3
4	30/06/2009		4
5	30/06/2009		5
6	30/06/2009		6
7
SUMPRODUCT

 

[FrankExcel]

thanks,

why sumproduct?

just

=PRODUCT(--(MONTH(K22:K37)=7),(M22:M37))

 

[Peter_SSs]

why sumproduct?

Because I mis-read your requirement.

just

=PRODUCT(--(MONTH(K22:K37)=7),(M22:M37))

Unless I'm still mis-interpreting, that doesn't do it either - see cell P22 below.

If you want to multiply the values in column M for the rows where the month in column K is July, then try the formula in O22. Note that it is an array formula so should be entered without the {} but confirmed with Ctrl+shift+Enter which will enclose the formula in {} as shown.

If this is not what you want, please explain what the correct solution for this sample data should be, and why.

Excel Workbook
	K	L	M	N	O	P
22	31/07/2009		2		24	5040
23	31/07/2009		3
24	31/07/2009		4
25	30/06/2009		5
26	30/06/2009		6
27	30/06/2009		7
28
PRODUCT

 

[FrankExcel]

thank you, the formula in 022 is exactly what I needed... I learned something that will save a lot of time and I appreciate it.