sum with multiple critera

[AlbertRBrowne]

I am using ths formula to sum unique values in a range
{=SUM(IF(FREQUENCY(IF($G$30:$G$279=$I$5,MATCH($F$30:$F$279,$F$30:$F$279,0)),ROW($G$30:$G$279)-ROW($G$30)+1),1))}
I now want to add another critera in that I only want to sum if the value in another column matches a given value. Can anyone help?
Thanks

Albert

 

[Matty]

Just add an additional IF, like:

=SUM(IF(FREQUENCY(IF($G$30:$G$279=$I$5,[COLOR=Red]IF($H$30:$H$279=$J$5,[/COLOR]MATCH($F$30:$F$279,$F$30:$F$279,0))[COLOR=Red])[/COLOR],ROW($G$30:$G$279)-ROW($G$30)+1),1))

Change the ranges I've chosen to suit your actual data.

Matty

 

[Aladin Akyurek]

I am using ths formula to sum unique values in a range
{=SUM(IF(FREQUENCY(IF($G$30:$G$279=$I$5,MATCH($F$30:$F$279,$F$30:$F$279,0)),ROW($G$30:$G$279)-ROW($G$30)+1),1))}
I now want to add another critera in that I only want to sum if the value in another column matches a given value. Can anyone help?
Thanks

Albert

1) Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($F$30:$F$279<>"",IF($G$30:$G$279=$I$5,MATCH($F$30:$F$279,$F$30:$F$279,0))),ROW($G$30:$G$279)-ROW($G$30)+1),1))

2) Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($F$30:$F$279<>"",IF($G$30:$G$279=$I$5,IF($H$30:$H$279=$J$5,MATCH($F$30:$F$279,$F$30:$F$279,0)))),ROW($G$30:$G$279)-ROW($G$30)+1),1))

which also tests whether $H$30:$H$279 is equal to $J$5.

 

[T. Valko]

I am using ths formula to sum unique values in a range
{=SUM(IF(FREQUENCY(IF($G$30:$G$279=$I$5,MATCH($F$30:$F$279,$F$30:$F$279,0)),ROW($G$30:$G$279)-ROW($G$30)+1),1))}
I now want to add another critera in that I only want to sum if the value in another column matches a given value. Can anyone help?
Thanks

Albert

Try this...

Sum unique numbers that correspond to "A"...

Book1
	A	B	C	D	E
2	A	10	_	A	25
3	A	15	_	_	_
4	A	10	_	_	_
5	A	15	_	_	_
6	B	20	_	_	_
7	C	5	_	_	_
8	C	7	_	_	_
9	C	7	_	_	_
10	C	10	_	_	_
Sheet1

This array formula** entered in E2:

=SUM(IF(FREQUENCY(IF(A2:A10=D2,B2:B10),B2:B10),B2:B10))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.