Resolved :: Formula for counting based on 3 criteria

pawebb

pawebb

Board Regular
Joined
Aug 10, 2007
Messages
136
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. :LOL:
example.xls
ABCDEFGH
1RMADateContractQTY
281617136Jan200731879841contractsJan2007Shouldequal
381622934Jan20073187984131879843
481628636Jan20071018636110186361
581628636Jan20071018636131879472
681625956Jan200731879471
781625956Jan200731879471
881617283Jan200731879471
981620564Jan200731879841
Sheet1


Thanks for looking...
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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!
 
Upvote 0
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!
Click to expand...

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...
 
Upvote 0
Exacly what I needed Domenic! Thanks.
Click to expand...

You're very welcome!

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

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.
Click to expand...

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

[=0]"";General

Hope this helps!
 
Upvote 0
...and changed the ranges to the entire column (ex. C:C).
Click to expand...

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.
Click to expand...

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.
Click to expand...

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

[=0]"";General
Click to expand...

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.

:LOL: Thanks again... :LOL:
 
Upvote 0
   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>
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
M
  • Solved
SUMIFS on unique rows
Replies
8
Views
454
Peter_SSs
Peter_SSs
J
  • Solved
Sum FTEs based on start/end date
Replies
3
Views
161
etaf
etaf
sharky12345
  • Solved
Count unique values with criteria (non-formula solution)
Replies
4
Views
202
Peter_SSs
Peter_SSs
N
  • Question
COUNTIF based on few different criteria
Replies
4
Views
110
awoohaw
awoohaw

Forum statistics

Threads
1,214,430
Messages
6,119,447
Members
448,898
Latest member
drewmorgan128

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top