SUMPRODUCT

[KII]

OK.

I have a sheet which holds a hardware inventory. In the sheet is a colunm which has the ship date of each peace of hardware. There is also a colunm which denotes what type it is (eg Server,Desktop or Laptop).

I need to create a formula that goes through the rows and counts all server, Desktops and Laptops and tell me the following:

servers older than 3 years
desktops older than 5 years
laptops older than 3 years


Can this be done using SUMPRODUCT or COUNTIF????

Please Help.

Regards,

Kev@stmerryn.co.uk

 

[SIXTH SENSE]

hi!
if you just need to count all server,laptop, etc. then countif is applicable. but if you want to know the item that is more than N years from shipment then sumproduct is mostlikely the answer.

 

[fairwinds]

Like this?

Book1
	A	B	C	D	E	F	G	H	I
1	Itemno	Type	Shipdate			Type	Yearsold	Count
2	1	server	30-jan-03			server	3	3
3	2	desktop	02-jan-00			desktop	5	0
4	3	laptop	03-jan-00			laptop	3	4
5	4	server	04-jan-00
6	5	desktop	05-jan-00
7	6	laptop	06-jan-00
8	7	server	07-jan-00
9	8	desktop	08-jan-00
10	9	laptop	09-jan-00
11	10	server	10-jan-00
12	11	desktop	11-jan-00
13	12	laptop	12-jan-00
Sheet2

 

[Aladin Akyurek]

Like this?

=SUMPRODUCT(($B$2:$B$13=F2)*($C$2:$C$13<=DATE(YEAR(NOW())-G2,MONTH(NOW()),DAY(NOW()))))

Bit shorter:

=SUMPRODUCT(--($B$2:$B$13=F2),--(DATEDIF($C$2:$C$13,TODAY(),"Y")>G2))

 

[fairwinds]

Completely forgot about DATEDIF

I wonder why they keep such a useful function "hidden".