Count columns contain data

%3CLINGO-SUB%20id%3D%22lingo-sub-1932429%22%20slang%3D%22en-US%22%3ECount%20columns%20contain%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932429%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20guys%2C%20i'm%20searching%20for%20some%20help%20with%20an%20Excel%20template%2C%20what%20I%20need%20in%20specific%20is%20to%20get%20or%20count%20columns%20instead%20of%20cells.%20I%20will%20try%20to%20explain%20my%20concern...%20in%20the%20excel%20file%20attached%20(Sample%20Data%20v1)%20you%20will%20see%20that%20there%20are%20several%20data%20where%20each%20column%20at%20Raw%206%20represent%201%20pc%20(A6%2C%20C6%2C%20E6...%20)%20until%20complete%2043%20pcs%2C%20each%20column%20have%2013%20data%20which%20represent%20specification%20data.%20If%20one%20data%20is%20out%20of%20spec%20then%20is%20marked%20as%20RED%20(spec%20is%20.118%20-%20.182)...%20now%20if%20you%20check%20the%20file%20there%20are%2011%20pcs%20(Columns)%20that%20out%20of%20specs%20no%20matter%20how%20many%20out%20of%20specs%20data%20have%20each%20part%20(some%20parts%20have%20more%20than%20one%20data%20out%20of%20specs)%20What%20I%20want%20to%20have%20is%20a%20formula%20to%20count%20how%20many%20parts%20(columns)%20are%20out%20of%20specs.%3C%2FP%3E%3CP%3EIn%20the%20example%20you%20will%20see%20that%20there%20are%2043%20pcs%20and%2011%20NOK%20(i%20know%20because%20i%20counted%20manually)%20but%20i%20want%20Excel%20count%20it%20for%20me%20with%20a%20formula.%3C%2FP%3E%3CP%3EHope%20some%20of%20you%20guys%20can%20help%20me%20with%20your%20experience.%20Let%20me%20know%20if%20further%20info%20or%20explanation%20is%20needed%2C%20thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1932429%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1932473%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20columns%20contain%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F881873%22%20target%3D%22_blank%22%3E%40RaulMTB%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20use%20a%20helper%20row.%20In%20the%20attached%20version%2C%20I%20used%20row%2021%2C%20but%20it%20can%20be%20any%20row.%3C%2FP%3E%0A%3CP%3EA21%20contains%20the%20formula%20%3DCOUNTIF(A8%3AA20%2C%22%26lt%3B0.118%22)%2BCOUNTIF(A8%3AA20%2C%22%26gt%3B0.182%22)%20and%20this%20has%20been%20copied%20to%20C21%2C%20E21%20etc.%3C%2FP%3E%0A%3CP%3EThe%20formula%20to%20count%20the%20number%20of%20columns%20with%20NOK%20is%20%3DCOUNTIF(21%3A21%2C%22%26gt%3B0%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934791%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20columns%20contain%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHi%2C%20i%20really%20appreciate%20your%20support%20and%20quick%20contribution...%20awesome!%20I%20will%20use%20it%20the%20formula%20thanks%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello guys, i'm searching for some help with an Excel template, what I need in specific is to get or count columns instead of cells. I will try to explain my concern... in the excel file attached (Sample Data v1) you will see that there are several data where each column at Raw 6 represent 1 pc (A6, C6, E6... ) until complete 43 pcs, each column have 13 data which represent specification data. If one data is out of spec then is marked as RED (spec is .118 - .182)... now if you check the file there are 11 pcs (Columns) that out of specs no matter how many out of specs data have each part (some parts have more than one data out of specs) What I want to have is a formula to count how many parts (columns) are out of specs.

In the example you will see that there are 43 pcs and 11 NOK (i know because i counted manually) but i want Excel count it for me with a formula.

Hope some of you guys can help me with your experience. Let me know if further info or explanation is needed, thanks!

 

 
 

 

 

2 Replies

@RaulMTB 

I'd use a helper row. In the attached version, I used row 21, but it can be any row.

A21 contains the formula =COUNTIF(A8:A20,"<0.118")+COUNTIF(A8:A20,">0.182") and this has been copied to C21, E21 etc.

The formula to count the number of columns with NOK is =COUNTIF(21:21,">0")

@Hans Vogelaar Hi, i really appreciate your support and quick contribution... awesome! I will use it the formula thanks a lot!