count cell if value present in every other cell + criteria
 

I have a spreadsheet where I am trying to count the number of cells that have
a value in them. this is for counting raw material on either skids or metal
coils that is in inventory.

Here is what I am trying to do:

I am trying to count the two types of material that we have; 'Skids' and
'Coils'. The user selects either 'Skids' or 'Coils' from list in cell B2. In
cell C2, the header is '# MATL' and contains the formula I need help with.
On the same row, cell G2 is 'MATL_1_WT', which if the item is a 'Coil' the
user enters the weight. The next cell H2 is 'MATL_1_CT' which if the item is
a 'Skid' the user enters the amount of material on a skid. This continues
alternating throughout H3 thru Z2 so H3 is 'MATL_2_WT', I2 is 'MATL_2_CT', J2
is 'MATL_3_WT', K2 is 'MATL_3_CT' etc.

I want the formula to act like this: if cell B2 contains 'Coils', then I
want the formula to count cells G2, I2, K2, M2, O2, Q2, S2, U2, W2 and Y2 and
return the number of cells that contain data. If cell B2 contains 'Skids',
then I want the formula to count cells H2, J2, L2, N2, P2, R2, T2, V2, X2 and
Z2 and return the number of cells that contain data.

I thought the following formula would work, but it is invalid:
=COUNT(IF(C2="Coil",G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2) ,(IF(C2="Skid",H2,J2,L2,N2,P2,R2,T2,V2,X2,Z2))

I look forward to seeing if anyone can help me out!

==IF(C2="Coil",COUNTA(G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y 2),IF(C2="Skid",COUNTA(H2,
J2,L2,N2,P2,R2,T2,V2,X2,Z2),""))

or

=SUMPRODUCT(--(G2:Z2<""),--(MOD(COLUMN(G2:Z2),2)=(IF(C2="Coil",1,0))))

--
HTH

Bob Phillips

"lrbest4x4xfar" wrote in message
...
I have a spreadsheet where I am trying to count the number of cells that
have
a value in them. this is for counting raw material on either skids or
metal
coils that is in inventory.

Here is what I am trying to do:

I am trying to count the two types of material that we have; 'Skids' and
'Coils'. The user selects either 'Skids' or 'Coils' from list in cell B2.
In
cell C2, the header is '# MATL' and contains the formula I need help with.
On the same row, cell G2 is 'MATL_1_WT', which if the item is a 'Coil' the
user enters the weight. The next cell H2 is 'MATL_1_CT' which if the item
is
a 'Skid' the user enters the amount of material on a skid. This continues
alternating throughout H3 thru Z2 so H3 is 'MATL_2_WT', I2 is 'MATL_2_CT',
J2
is 'MATL_3_WT', K2 is 'MATL_3_CT' etc.

I want the formula to act like this: if cell B2 contains 'Coils', then I
want the formula to count cells G2, I2, K2, M2, O2, Q2, S2, U2, W2 and Y2
and
return the number of cells that contain data. If cell B2 contains
'Skids',
then I want the formula to count cells H2, J2, L2, N2, P2, R2, T2, V2, X2
and
Z2 and return the number of cells that contain data.

I thought the following formula would work, but it is invalid:

=COUNT(IF(C2="Coil",G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2) ,(IF(C2="Skid",H2,J2,L2,N2
,P2,R2,T2,V2,X2,Z2))

I look forward to seeing if anyone can help me out!

=SUMPRODUCT(--(MOD(COLUMN(G2:Z2)-COLUMN(G2)+1-(B2="Coils"),2)=0),--(G2:Z2<""))

lrbest4x4xfar wrote:
I have a spreadsheet where I am trying to count the number of cells that have
a value in them. this is for counting raw material on either skids or metal
coils that is in inventory.

Here is what I am trying to do:

I am trying to count the two types of material that we have; 'Skids' and
'Coils'. The user selects either 'Skids' or 'Coils' from list in cell B2. In
cell C2, the header is '# MATL' and contains the formula I need help with.
On the same row, cell G2 is 'MATL_1_WT', which if the item is a 'Coil' the
user enters the weight. The next cell H2 is 'MATL_1_CT' which if the item is
a 'Skid' the user enters the amount of material on a skid. This continues
alternating throughout H3 thru Z2 so H3 is 'MATL_2_WT', I2 is 'MATL_2_CT', J2
is 'MATL_3_WT', K2 is 'MATL_3_CT' etc.

I want the formula to act like this: if cell B2 contains 'Coils', then I
want the formula to count cells G2, I2, K2, M2, O2, Q2, S2, U2, W2 and Y2 and
return the number of cells that contain data. If cell B2 contains 'Skids',
then I want the formula to count cells H2, J2, L2, N2, P2, R2, T2, V2, X2 and
Z2 and return the number of cells that contain data.

I thought the following formula would work, but it is invalid:
=COUNT(IF(C2="Coil",G2,I2,K2,M2,O2,Q2,S2,U2,W2,Y2) ,(IF(C2="Skid",H2,J2,L2,N2,P2,R2,T2,V2,X2,Z2))

I look forward to seeing if anyone can help me out!

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.