Vlookup formulas with criteria

[Guest]

I am trying to create three different values based on the same data and need
to break out totals by criteria on one tab with the forumulas and datasets on
other tabs. I have the sum for all values correct doing a Vlookup formula
that will return a blank value instead of #N/A :
=IF(ISNA(VLOOKUP($A18,Jan!$M$3:$S$25,7,0)),0,VLOOKUP($A18,Jan!$M$3:$S$25,7,0))

Now how do I add on conditions to get the sum of the products I want instead
of the totals, example how many cars were sold from a dealer, how many trucks
and how man vans? I know how many vehicles, but now need to break it down by
criteria on the monthly sales tab in the column next to the sales totals.

Any help is appreciated.

 

[Guest]

Patricia,
I guess the VLOOKUP will not give you a sum - only the first entry...

However, for your use, use a sumproduct function
=sumproduct(A1:A50="DealerA",B1:B50=Truck",C1:C50)
assuming column a has the month, column b the type and column c the amount
of sales or with the information from your post
=sumproduct(Jan!$M$3:$M$25=$A18,Jan!$N$3:$N$25="Truck",Jan!$S$3:$S$25)

 

[Guest]

Hi,

Column A does not have the month, each month is a separate tab as it is an
automatic download feed from sales system.

My Column A is the Dealer Name, which I was trying to match using Vlookup on
the sales feed sheet column D. Sales are in Column E and Model is Column F
each month.

 

[Peo Sjoblom]

Either SUMPRODUCT as earlier stated

=SUMPRODUCT--(A2:A100="Dealer1"),--(F2:F100="T-Ford"),E2:E100)

will total sales for Dealer1 and T-Ford

Put one of these in each sheet to get the different months


adapt to fit

 

[Guest]

Well, I couldn't get the Sumproduct to work correctly but ended up with these
two:
=SUM(IF(Jan!$E$2:$E$200=$A35,Jan!$F$2:$F$200, 0)) to sum the monthly sales and
=SUM(IF(Jan!$E$2:$E$200=$A59,IF(Jan!$G$2:$G$200>"Pickup",Jan!$F$2:$F$200,
0)),0) to sum the sales of pickups at a particular dealership listed on A59

But is there a way to make it "contain" the work Car, since there are
sedans, station wagons, hybrids, etc?

 

[Guest]

Solved it by doing the following:
{=SUM(IF(Jan!$E$2:$E$200=$A18,IF(Jan!$G$2:$G$200<="Car*",Jan!$F$2:$F$200,
0)),0)}