Somewhat 'complex' count if statement?

LeShin

New Member
Joined
Mar 25, 2014
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi, I want a way to do some analysis of a large data set , best way to explain is to show a little of the extract:

25.4 RPT_APPLICATION_D
25.4 RPT_APPLICATION_D
25.4 RPT_APPLICATION_D
25.5 RPT_ASSIGNED_AGENTS_D
25.5 RPT_ASSIGNED_AGENTS_D
25.6 RPT_CASH_ACCOUNT_D
25.6 RPT_CASH_ACCOUNT_D
25.6 RPT_CASH_ACCOUNT_D

Basically I want to be able to count the no of times in this example that 'RPT' appears in column B but ONLY when column A's Index no is unique i.e. I want the formula to return '3' rather than '8'
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have available.
 
Upvote 0
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0
Hi, I want a way to do some analysis of a large data set , best way to explain is to show a little of the extract:

25.4 RPT_APPLICATION_D
25.4 RPT_APPLICATION_D
25.4 RPT_APPLICATION_D
25.5 RPT_ASSIGNED_AGENTS_D
25.5 RPT_ASSIGNED_AGENTS_D
25.6 RPT_CASH_ACCOUNT_D
25.6 RPT_CASH_ACCOUNT_D
25.6 RPT_CASH_ACCOUNT_D

Basically I want to be able to count the no of times in this example that 'RPT' appears in column B but ONLY when column A's Index no is unique i.e. I want the formula to return '3' rather than '8'
Oh apologies , Office 2016
 
Upvote 0
In that case how about
+Fluff New.xlsm
AB
1
225.4RPT_APPLICATION_D
325.4RPT_APPLICATION_D
425.4RPT_APPLICATION_D
525.5RPT_ASSIGNED_AGENTS_D
625.5RPT_ASSIGNED_AGENTS_D
725.6RPT_CASH_ACCOUNT_D
825.6RPT_CASH_ACCOUNT_D
925.6RPT_CASH_ACCOUNT_D
10
113
Main
Cell Formulas
RangeFormula
B11B11=SUM(--(FREQUENCY(IF(LEFT(B2:B9,3)="RPT",A2:A9),A2:A9)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.


PS please don't forget to update your account details. ;)
 
Upvote 0
In that case how about
+Fluff New.xlsm
AB
1
225.4RPT_APPLICATION_D
325.4RPT_APPLICATION_D
425.4RPT_APPLICATION_D
525.5RPT_ASSIGNED_AGENTS_D
625.5RPT_ASSIGNED_AGENTS_D
725.6RPT_CASH_ACCOUNT_D
825.6RPT_CASH_ACCOUNT_D
925.6RPT_CASH_ACCOUNT_D
10
113
Main
Cell Formulas
RangeFormula
B11B11=SUM(--(FREQUENCY(IF(LEFT(B2:B9,3)="RPT",A2:A9),A2:A9)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.


PS please don't forget to update your account details. ;)
Many thanks, I'll try this....and account details updated with office version :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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