COUNTIF using multiple criteria

P

patayloruk

I am trying to get a count of each different wire size in miltiple connectors.
In column A of sheet WIRE DIAMETER I have each unique connector number used listed and in row 1 I have each cable type listed.

eg. A2 contains "DP1000M" C1 contains "12-1C"

In sheet Macro1 (a wire list), column D contains the connector number on each row eg. DP1000M and column F contains the wire type on each row.

In C2 I am trying to get a count of all 12-1C cables that are in DP1000M, in C2 all 12-1C wires in DP1001M etc....and in D2 all 14-1C wires in DP1000M etc...

I tried this formula:
=COUNTIF(Macro1!$F:$F,IF(Macro1!$D:$D='WIRE DIAMETER'!$A2,('WIRE DIAMETER'!C$1),0))
but when I look at the results (coppied down and across) it counts ALL the wires instead of just the ones for DP1000M and the count is only shown on row 6 for DP1004M

Hope this makes sense and look forward to a reply.

Thanks

Paul Taylor
 
G

Guest

Hi

Not entirely sure what you are after but I it sounds
similar to some stuff that I have worked on recently.
What I did was to use a seperate concatenate function to
create unique ids for each aspect. In your case you could
concatenate A2 and C1 in sheet Wire Diameter and thus
creating the unique value "DP1000M12-1C". If you do the
same in your Macro 1 sheet then you will have only one
condition to use in the countif formula. There may well
be better ways of doing this but this too should work.

Cheers
Andy

-----Original Message-----
I am trying to get a count of each different wire size in miltiple connectors.
In column A of sheet WIRE DIAMETER I have each unique
Click to expand...
connector number used listed and in row 1 I have each
cable type listed.
eg. A2 contains "DP1000M" C1 contains "12-1C"

In sheet Macro1 (a wire list), column D contains the
Click to expand...
connector number on each row eg. DP1000M and column F
contains the wire type on each row.
In C2 I am trying to get a count of all 12-1C cables that
Click to expand...
are in DP1000M, in C2 all 12-1C wires in DP1001M
etc....and in D2 all 14-1C wires in DP1000M etc...
I tried this formula:
=COUNTIF(Macro1!$F:$F,IF(Macro1!$D:$D='WIRE DIAMETER'!$A2, ('WIRE DIAMETER'!C$1),0))
but when I look at the results (coppied down and across)
Click to expand...
it counts ALL the wires instead of just the ones for
DP1000M and the count is only shown on row 6 for DP1004M
 
F

Frank Kabel

Hi
try in C2
=SUMPRODUCT(--('Macro1'!$D$1:$D$1000=$A$2),--('Macro1'!$D$1:$D$1000=$C$
1))
 
D

Domenic

Hi Paul,

C2, copied across and down:

=SUMPRODUCT(--(Macro1!$D$2:$D$100='WIRE
DIAMETER'!$A2),--(Macro1!$F$2:$F$100='WIRE DIAMETER'!C$1))

Hope this helps!
 
P

patayloruk

Thankyou everyone

I used (Extract form cell C35) =SUMPRODUCT(--(Macro1!$D$1:$D$1000=$A35),--(Macro1!$F$1:$F$1000=C$1))

Many thanks again, works well
Regards
Paul
 
Top