Prev: Countif function
Next: Why won't this function work?
From: RLD on 11 Mar 2010 18:33
I have 2 sheets in one workbook (Sheet 1 and Sheet 2)

Sheet 2 has 3 columns:
A B C
MAKE TYPE QTY

1 toyota compact 10
2 ford pickup 15
3 toyota sedan 20
4 toyota pickup 80
5 nissan hybrid 10

Sheet 1 has 2 columns:
A B
MAKE PICKUPS

1 toyota ? (SUM)

I need Sheet 1,B1 to calculate the total number of matching items in sheet 2
that matches the data entered in Sheet 1,A1. In other words, I need sheet
1,B1 to automatically sum up the total number of toyota pickups in sheet 2.
Can anyone help me with the formula to accomplish this?

From: Bob Phillips on 11 Mar 2010 19:11
Try

=SUMPRODUCT(--(Sheet2!$A$2:$A$200=A2),--(Sheet2!$B$2:$B$200="pickup"),Sheet2!$C$2:$C$200)

--

HTH

Bob

"RLD" <RLD(a)discussions.microsoft.com> wrote in message
news:9AE85F80-6AEF-4E0D-83C6-A3AEF53A82B7(a)microsoft.com...
>I have 2 sheets in one workbook (Sheet 1 and Sheet 2)
>
> Sheet 2 has 3 columns:
> A B C
> MAKE TYPE QTY
>
> 1 toyota compact 10
> 2 ford pickup 15
> 3 toyota sedan 20
> 4 toyota pickup 80
> 5 nissan hybrid 10
>
> Sheet 1 has 2 columns:
> A B
> MAKE PICKUPS
>
> 1 toyota ? (SUM)
>
> I need Sheet 1,B1 to calculate the total number of matching items in sheet
> 2
> that matches the data entered in Sheet 1,A1. In other words, I need sheet
> 1,B1 to automatically sum up the total number of toyota pickups in sheet
> 2.
> Can anyone help me with the formula to accomplish this?
>


 | 
Pages: 1
Prev: Countif function
Next: Why won't this function work?