Formula needed for lookup

[bbenaway]

Attached is a .jpg of what I am needing. Have a large database the track both laptops and worstation computers. We are wanting to look at column E for the type of computer (wks or laptop) and also column I ( year placed into service). The formula would be set to look at a particular year and type computer and give us a count of how many that would need to be replace. Example: laptop and 2007 for a total of 2.

Would like to take this to the next step and have a drop down menu to pick the computer type and/or year to change the count total in the same cell.

 

[SkipVought]

Hi,

I'd first suggest normalizing your data. It may be nice to look at, having a table for executives and a table for other, but it makes doing what you need to do alot more difficult and cumbersome.

By normalizing, I mean append the data together, under ONE set of headings, addinj a NEW COLUMN (J) that will contain either Executive or Other.

From a single table you could use the PivotTable wizard to summarize the TYPE for each MFR DATE, in about 5 seconds. If you need to make fancy controls, that can be also accomplished, in a proportionaltely larger time frame.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Let's assume that the Type selection is in A1, and that the Year selection is in A2

=SUMPRODUCT((E1:E2500=$A$1)*(I1:I2500=$A$2))

Make sure the references reach down far enough

 

[SkipVought]

Gruuuu's solution is certainly a valid approch.

However, if you also wanted to include counts for executive or other, you would need to do something else.

IMHO, it is always better to structure your data properly, from which generating a report of Executives or Other or anything else would be significantly simpler.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I'll definitely agree with Skip here. If you want to make generating information from your data easier, consider restructuring your data.

It wouldn't even need to change much, just add a new column, call it whatever you like, and just put "Other" or "Exec" in it. Then you can keep all of your data in one place (table) and build reports off of that.

If your collection of data is large, as you say, this will help a lot.