COUNTIF for a column With Limits from a second column

krayzken

New Member
Joined
Mar 22, 2014
Messages
1
I'm a welding inspector trying to develop a weld tracking sheet and am in need of a formula that counts types of welds from column-B but without duplicating a weld number from Column A. For example:

Weld#. weldType. Inspection. #ofwelds
A. B. C. SW = 2
weld1. BW. Step1. BW = 2
weld2. SW. Step1
weld3. SW. Step1
weld4. BW. Step1
weld2. SW. Step2
weld4. BW. Step2
weld4. BW. Step3


we are tracking the welds by the inspection being conducted and will have 6-7000 welds, however with all the inspections have 3-4 steps each, we will have estimated 11,0000 BW's showing.. Thanks in advance guys.. New to this...

The closed I came was something like this =COUNTIF(Frequency(B1:B10,"BW"), but not if ????
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
How about making a helper column D with A1&B1 then after the 7000 instances just do a remove duplicate on the column then apply the countif formula.

Cheers.

PS. Hope someone can do exactly what you ask but mine is easy enough
 
Upvote 0
Sry before removing duplicates copy the B column to E so you keep your records in tack and then apply remove duplicates to column D and E. Then on the reduced list do =COUNTIF(e:e, "BW.") and that does work :)
 
Upvote 0
I'm a welding inspector trying to develop a weld tracking sheet and am in need of a formula that counts types of welds from column-B but without duplicating a weld number from Column A. For example:

Weld#. weldType. Inspection. #ofwelds
A. B. C. SW = 2
weld1. BW. Step1. BW = 2
weld2. SW. Step1
weld3. SW. Step1
weld4. BW. Step1
weld2. SW. Step2
weld4. BW. Step2
weld4. BW. Step3


we are tracking the welds by the inspection being conducted and will have 6-7000 welds, however with all the inspections have 3-4 steps each, we will have estimated 11,0000 BW's showing.. Thanks in advance guys.. New to this...

The closed I came was something like this =COUNTIF(Frequency(B1:B10,"BW"), but not if ????
Control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($A$2:$A$100<>"",
  IF($B$2:$B$100="BW",MATCH($A$2:$A$100,$A$2:$A$100,0))),
  ROW($A$2:$A$100)-ROW($A$2)+1),1))
 
Upvote 0
Control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($A$2:$A$100<>"",
  IF($B$2:$B$100="BW",MATCH($A$2:$A$100,$A$2:$A$100,0))),
  ROW($A$2:$A$100)-ROW($A$2)+1),1))

I tried it and works great job Aladin. I don't know how it works but it does. More research required as I never used the FREQUENCY term and am a bit vague on match and row but I'm sure i'll get there in the end :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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