Vlookup formulas with criteria
 

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,VLOOK UP($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.

Vlookup formulas with criteria
 

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="Tr uck",Jan!$S$3:$S$25)

"Patricia" wrote:

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,VLOOK UP($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.

Vlookup formulas with criteria
 

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.

"rdwj" wrote:

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="Tr uck",Jan!$S$3:$S$25)

"Patricia" wrote:

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,VLOOK UP($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.

Vlookup formulas with criteria
 

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

--
Regards,

Peo Sjoblom

"Patricia" wrote in message
...
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.

"rdwj" wrote:

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="Tr uck",Jan!$S$3:$S$25)

"Patricia" wrote:

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,VLOOK UP($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.

Vlookup formulas with criteria
 

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"P ickup",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?

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

"Patricia" wrote in message
...
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.

"rdwj" wrote:

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="Tr uck",Jan!$S$3:$S$25)

"Patricia" wrote:

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,VLOOK UP($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.

Vlookup formulas with criteria
 

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)}



"Patricia" wrote:

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"P ickup",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?

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom

"Patricia" wrote in message
...
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.

"rdwj" wrote:

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="Tr uck",Jan!$S$3:$S$25)

"Patricia" wrote:

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,VLOOK UP($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.