nested countif, sumif, lookup 1

[creeker69]

I am having trouble using nested functions. I know there is a way to do this simply, but maybe I am too simple. Here's my data:

DAYS TYPE DATE AMOUNT
107 CAR 10/16/03 10,621.68
107 CAR 10/16/03 12,055.39
100 CAR 10/23/03 10,586.03
99 CAR 10/24/03 5,118.00
95 CAR 10/28/03 17,488.69
32 CAR 12/30/03 1,149.50
32 CAR 12/30/03 12,231.69
32 CAR 12/30/03 1,200.00
31 CAR 12/31/03 17,532.03
169 TRK 08/15/03 7,000.00
114 TRK 10/09/03 17,694.80
107 TRK 10/16/03 14,468.28
107 TRK 10/16/03 15,071.35
99 TRK 10/24/03 20,616.40
95 TRK 10/28/03 26,589.26
95 TRK 10/28/03 2,052.50
31 TRK 12/31/03 11,337.64
31 TRK 12/31/03 9,157.09

Here's the result I need.


NUMBER AMOUNT
CARS < 45 4 32,113.22
CARS > 45 5 55,869.79
TRKS < 45 2 20,494.73
TRKS > 45 7 103,492.59

Thanks in advance for your help.

 

[luceze]

For the count you can use this formula (array entered,ctrl+shift+enter):

=SUM(IF(Type=&quot;CAR&quot;,IF(Days<45,1)))

For the sum use this formula (also array entered

=SUM(IF(Type=&quot;CAR&quot;,IF(Days<45,$E$5:$E$22)))

Where &quot;Type&quot; is the range of The vehicle type, and &quot;Days&quot; refers to the range of the number of days.


HTH,
Eric

 

[creeker69]

Eric,

That's waht I was missing. The Array! That works perfectly and is a huge help on this project and more!

Thanks,
Creeker