# Resolved :: Formula for counting based on 3 criteria

#### pawebb

##### Board Regular
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
ABCDEFGH
281617136Jan200731879841contractsJan2007Shouldequal
381622934Jan20073187984131879843
481628636Jan20071018636110186361
581628636Jan20071018636131879472
681625956Jan200731879471
781625956Jan200731879471
881617283Jan200731879471
981620564Jan200731879841
Sheet1

Thanks for looking...

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Domenic

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
...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

##### Active Member
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>

Replies
13
Views
457
Replies
1
Views
264
Replies
3
Views
363
Replies
1
Views
639
Replies
5
Views
160

### Forum statistics

1,191,057
Messages
5,984,408
Members
439,884
Latest member
BrownEyedGirl ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back