Adding values based on a VLOOKUP?

  • Thread starter Thread starter Kevin M
  • Start date Start date
K

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
 
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)
 
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
 
Manish, in what context? =sumproduct(E:E,H:H) gives a
#value error.
Thanks for the input
Kevin M
 
Hi Kevin

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

HTH
Frank
 
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

Kevin said:
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
Click to expand...


.
Click to expand...
 
Back
Top