Resolved :: Formula for counting based on 3 criteria

[pawebb]

Need a little help writing a formula that will calculate the number of times a contract is used each month. Problem is that RMA numbers sometimes repeat. So I need to count the contract number for every unique RMA in a month.

example.xls
	A	B	C	D	E	F	G	H
1	RMA	Date	Contract	QTY
2	81617136	Jan2007	3187984	1		contracts	Jan2007	Shouldequal
3	81622934	Jan2007	3187984	1		3187984		3
4	81628636	Jan2007	1018636	1		1018636		1
5	81628636	Jan2007	1018636	1		3187947		2
6	81625956	Jan2007	3187947	1
7	81625956	Jan2007	3187947	1
8	81617283	Jan2007	3187947	1
9	81620564	Jan2007	3187984	1
Sheet1

Thanks for looking...

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

H3, copied down:

=SUM(IF(FREQUENCY(IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,$A$2:$A$9)),IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,$A$2:$A$9))),1))

To allow empty cells in Column A, try the following instead...

=SUM(IF(FREQUENCY(IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,IF($A$2:$A$9<>"",$A$2:$A$9))),IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,IF($A$2:$A$9<>"",$A$2:$A$9)))),1))

Hope this helps!

 

[pawebb]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

H3, copied down:

=SUM(IF(FREQUENCY(IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,$A$2:$A$9)),IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,$A$2:$A$9))),1))

To allow empty cells in Column A, try the following instead...

=SUM(IF(FREQUENCY(IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,IF($A$2:$A$9<>"",$A$2:$A$9))),IF($C$2:$C$9=F3,IF($B$2:$B$9=$G$2,IF($A$2:$A$9<>"",$A$2:$A$9)))),1))

Hope this helps!

Exacly what I needed Domenic! Thanks. I ended up using the second formula and changed the ranges to the entire column (ex. C:C). I also added an if statement so that if it equaled 0 it left the cell blank instead of posting the 0.

Thanks again...

 

[Domenic]

Exacly what I needed Domenic! Thanks.

You're very welcome!

...and changed the ranges to the entire column (ex. C:C).

Actually, whole column references should return #NUM!. And if you're using a near whole column reference, such as C2:C65536, the formula will be inefficient. Instead, if you're using Excel 2003 or later version, convert your data into a list (Data > List > Create List). The ranges will automatically adjust as data is added/removed. Alternatively, use dynamic named ranges.

I also added an if statement so that if it equaled 0 it left the cell blank instead of posting the 0.

Instead of using an IF statement, try custom formatting (Format > Cell > Number...) the cell as follows...

[=0]"";General

Hope this helps!

 

[pawebb]

...and changed the ranges to the entire column (ex. C:C).

Actually, whole column references should return #NUM!. And if you're using a near whole column reference, such as C2:C65536, the formula will be inefficient. Instead, if you're using Excel 2003 or later version, convert your data into a list (Data > List > Create List). The ranges will automatically adjust as data is added/removed. Alternatively, use dynamic named ranges.

I did receive the #NUM! error. I forgot I backed it down to cover 500 rows, but I like your idea on creating a list so I will give that a shot.

I also added an if statement so that if it equaled 0 it left the cell blank instead of posting the 0.

Instead of using an IF statement, try custom formatting (Format > Cell > Number...) the cell as follows...

[=0]"";General

I think you meant (Format > Cell > Custom). Either way it worked like a charm. I didn't even know this was possible. This will save me a tone of time and I assume it will make everything more efficient.

Thanks again...

 

[theozz]

   Hi ! Here Suggest to you
<TABLE style="BORDER-COLLAPSE: collapse " cellSpacing=1 cellPadding=1 border=3><COLGROUP><colspan=13 width=1040 rowspan=127 height=2286><TBODY><TR height=18><TD align=center bgColor=#969696>R/C</TD><TD colSpan=1 width=510 align=center bgColor=#33cccc>A</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>B</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>C</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>D</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>E</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>F</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>G</TD><TD colSpan=1 align=center width=510 bgColor=#33cccc>H</TD><TD colSpan=1 align=center width=529.8 bgColor=#33cccc>I</TD></TR><TR height=18><TD align=center bgColor=#33cccc>1</TD><TD align=center>RMA</TD><TD colSpan=1 align=center>Date</TD><TD colSpan=1 align=center>Contract</TD><TD colSpan=1 align=center>QTY</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>2</TD><TD align=center>81617136</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187984</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>contracts</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>Should equal</TD><TD colSpan=1 align=center>Formula</TD></TR><TR height=18><TD align=center bgColor=#33cccc>3</TD><TD align=center>81622934</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187984</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>3187984</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>3</TD><TD colSpan=1 align=center bgColor=#ccffcc>2</TD></TR><TR height=18><TD align=center bgColor=#33cccc>4</TD><TD align=center>81628636</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>1018636</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>1018636</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center bgColor=#ccffcc>1</TD></TR><TR height=18><TD align=center bgColor=#33cccc>5</TD><TD align=center>81628636</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>1018636</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center>2</TD><TD colSpan=1 align=center bgColor=#ccffcc>2</TD></TR><TR height=18><TD align=center bgColor=#33cccc>6</TD><TD align=center>81625956</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>7</TD><TD align=center>81625956</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>8</TD><TD align=center>81617283</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>9</TD><TD align=center>81617283</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>10</TD><TD align=center> </TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>11</TD><TD align=center>81617283</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>12</TD><TD align=center> </TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR><TR height=18><TD align=center bgColor=#33cccc>13</TD><TD align=center>81617283</TD><TD colSpan=1 align=center>Jan 2007</TD><TD colSpan=1 align=center>3187947</TD><TD colSpan=1 align=center>1</TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD><TD colSpan=1 align=center> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-COLLAPSE: collapse " cellSpacing=1 cellPadding=1 border=5><COLGROUP><colspan=5 width=500 rowspan=4 height=72><TBODY><TR height=18><TD align=center colSpan=5>The Formula is below...(With <U> Running MicrosoftExcel Ver 97</U>)
<FONT color=#ff00ff>If Pink Formula that is FormulaArray</FONT> !!!
WithOut {} Write Formula and<FONT color=#000080> Control+Shift+Enter</FONT></TD></TR><TR height=18><TD align=center width=30 bgColor=#99cc00>No</TD><TD align=center width=55 bgColor=#99cc00>Address</TD><TD align=center width=90 bgColor=#99cc00>Answer</TD><TD align=center width=400 bgColor=#99cc00>Formula / Additional talking</TD><TR height=18><TD align=center width=30>1</TD><TD align=center width=55>I3</TD><TD align=center width=90>2</TD><TD 400><FONT color=#ff00ff>=SUM((IF($A$1:$A$13="",0,IF(ROW($A$1:$A$13)=MATCH($A$1:$A$13,$A$1:$A$13,0),1,0)))*($C$1:$C$13=F3))</FONT></TD><TR height=18><TD align=center width=30>2</TD><TD align=center width=55>I4</TD><TD align=center width=90>1</TD><TD 400><FONT color=#ff00ff>=SUM((IF($A$1:$A$13="",0,IF(ROW($A$1:$A$13)=MATCH($A$1:$A$13,$A$1:$A$13,0),1,0)))*($C$1:$C$13=F4))</FONT></TD><TR height=18><TD align=center width=30>3</TD><TD align=center width=55>I5</TD><TD align=center width=90>2</TD><TD 400><FONT color=#ff00ff>=SUM((IF($A$1:$A$13="",0,IF(ROW($A$1:$A$13)=MATCH($A$1:$A$13,$A$1:$A$13,0),1,0)))*($C$1:$C$13=F5))</FONT></TD></TR><TR height=18><TD align=center colSpan=5>
<FONT color=#ff00ff>If Pink Formula that is FormulaArray</FONT> !!!
WithOut {} Write Formula and<FONT color=#000080> Control+Shift+Enter</FONT></TD></TR></TBODY></TABLE>