Vlookup formulas with criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
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.
 
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
 
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?
 
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)}
 
Back
Top