COUNTIF for a column With Limits from a second column

[krayzken]

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 ????

 

[ace19852]

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

 

[ace19852]

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

 

[Aladin Akyurek]

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:

=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))

 

[ace19852]

Control+shift+enter, not just enter:

=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

 

[Aladin Akyurek]

I tried it and works great job Aladin.

You are welcome.

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

Here is an explanatory link:

http://www.mrexcel.com/forum/excel-...ing-sum-if-frequency-match-2.html#post3156949