Adding values based on a VLOOKUP?

[Kevin M]

Hey all, got a stumper here.
I need to add values provided they meet the criteria.

In Col E i have a part number, in H,I,J,and K, the parts'
inventory location can be in one of these four possible
cols by putting a 1 in the appropriate cell.

H=in stock
i=on a tool
j=in repair
k=scrap

the part numbers can have more than one entry, anywhere
from 1 to >100. I've created a simple countif formula to
get an overall total, but i'm having trouble getting a
broken down tally for each of the 4 columns onto a
reporting page where the user can insert the part number
and get a broken down total for the part number he
inserts.

Any suggestions?
TIA
Kevin M

 

[Bob Phillips]

Kevin ,

Have you tried COUNTIF

=COUNTIF(E:E,"H")

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Manish Bajpai]

Instead use , sumproduct formula.

 

[Kevin M]

Bob, countif will answer the total count of similar part
numbers only. i need to count the number of 1's in H if
they correspond with the part number (Col E) in question.
Anything else i can try?
Thanks for the input.
Kevin

 

[Guest]

Manish, in what context? =sumproduct(E:E,H:H) gives a
#value error.
Thanks for the input
Kevin M

 

[Frank Kabel]

Hi Kevin

you can use SUMPRODUCT
=SUMPRODUCT(($E$1:$E$9999=[partnumber])*($H$1:$H$9999=1))

HTH
Frank

 

[Kevin M]

Thanks Frank, just what the doctor ordered!

-----Original Message-----
Hi Kevin

you can use SUMPRODUCT
=SUMPRODUCT(($E$1:$E$9999=[partnumber])*($H$1:$H$9999=1))

HTH
Frank

Bob, countif will answer the total count of similar part
numbers only. i need to count the number of 1's in H if
they correspond with the part number (Col E) in question.
Anything else i can try?
Thanks for the input.
Kevin

.