excel-count a value in 1 column if a value in a 2nd is true

[Jim C.]

I need to calculate the sum of a cable part number (included in column D3
thru D42) DEPENDENT ON the length of the cable in the adjacent (E) column

 

[Shane Devenshire]

Hi,

You haven't given us much detail, so why not try COUNTIF(Range,Condition)

Note your subject line says you want to COUNT, your message says you want to
SUM??

SUMIF(Range,Condition,SumRange)

 

[Pete_UK]

Could you expand on this? Do you need the COUNT of the number of a
particular cable part num which also has a certain length? Will you
use a cell to hold the part number and another to hold the length? If
so which cells?

Pete

 

[T. Valko]

Not real clear what you want.

Do you want the total length of cable for a specific part number?

Try something like this...

G3 = some part number

=SUMIF(D342,G3,E3:E42)

 

[Jim C.]

Not real clear what you want.

Do you want the total length of cable for a specific part number?

Try something like this...

G3 = some part number

=SUMIF(D342,G3,E3:E42)

--
Biff
Microsoft Excel MVP





Sorry my question was not clear. I tried "countif" and "countifs" prior to my first post, but got error messages.

Let me try to be more clear.

In the first column I have three different cable part numbers (call them x,
y & Z), and adjacent to each number is the length of the cable (0.25m, 0.5m,
1m, 2m, etc).

Like this:

| D | E
2|Cable # | Length
3| x | 0.25m
4| z | 2m
5| x | 1m
6| x | 0.25m
7| y | 2m
8| x | 0.25m

I need to determine the quantity of part number/length for a bill of
material. I have several sheets in this document, so the quantities will
grow quite large, and I need to automate the process for speed and accuracy.

Does this help?

 

[T. Valko]

So for PN X you want to know there are three 0.25m cables?

I tried "countif" and "countifs"

So you're using Excel 2007?

G3 = some part number like x
H3 = some size like 0.25m

=COUNTIFS(D342,G3,E3:E42,H3)

This one will work in any version of Excel:

=SUMPRODUCT(--(D342=G3),--(E3:E42=H3))