Lookup

[Gazz_85]

I have a table that i want to automatically up date, not sure how to do it. I
want it to look through another table and everytime it finds a certain value,
i want it to check another colum to see if it has a certain value and count
the number.

eg look up colum A looking for the number 3760 and for every row with 3760
then look in column B to see if it has the value "Ex" and if it does count it
(add one to the total) and then put the final value in my other table to be
used for reporting (charts,graphs,etc)

Is this possible???

Cheers

 

[Jacob Skaria]

Use SUMPRODUCT()
=SUMPRODUCT(--($A$1:$A$100=3760),--($B$1:$B$100="Ex"))

You can change 3760 and "Ex" to cell references and add Sheetnames (if table
is in different sheet) to suit your requirement

If this post helps click Yes

 

[Pete_UK]

Suppose you have 3760 in D1 - put this formula in E1:

=SUMPRODUCT((A1:A1000=D1)*(B1:B1000="Ex"))

and this will count the number of rows that contain both "Ex" in
column B and the value from D1 in column A.

Hope this helps.

Pete