Prev: Text Changing to Symbol
Next: not printing correctly
From: Faiz on 3 Nov 2009 12:51
Example:

Monthly Exp.xlsx

Cloumn 1 Col2 Col3
Expense Heads Dept Amount
5121 100 Formula(Need the
data here)
5122 100 Formula(Need the
data here)
5123 100 Formula(Need the
data here)



Jan09.xlsx

Expense Heads Dept Amount
5121 100 6500.00
5122 100 1215.00
5123 100 200.00
5121 120 3500.00
5122 120 1215.00
5123 120 3250.00
5121 140 2500.00
5122 140 1215.00
5123 140 3150.00

I need in worksheet 1 i.e Monthly exp in column 3 to look(bring) the amount
from sheet Jan09, amount of 5121 where the dept is 100.

It is clearly visible that 5121 head is present with dept 100,120 & 140 in my
example

Can anyone please help me on this?

Hope that is clear.
From: Jacob Skaria on 3 Nov 2009 12:56
When you have multiple criteria use SUMPRODUCT()

=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2),C1:C10)

If you are using Excel 2007 you can use SUMIFS() to acheive the same result

=SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2)

'or with cells F1 and F2 holding the criteria
=SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2)

If this post helps click Yes
---------------
Jacob Skaria


"Faiz" wrote:

> Example:
>
> Monthly Exp.xlsx
>
> Cloumn 1 Col2 Col3
> Expense Heads Dept Amount
> 5121 100 Formula(Need the
> data here)
> 5122 100 Formula(Need the
> data here)
> 5123 100 Formula(Need the
> data here)
>
>
>
> Jan09.xlsx
>
> Expense Heads Dept Amount
> 5121 100 6500.00
> 5122 100 1215.00
> 5123 100 200.00
> 5121 120 3500.00
> 5122 120 1215.00
> 5123 120 3250.00
> 5121 140 2500.00
> 5122 140 1215.00
> 5123 140 3150.00
>
> I need in worksheet 1 i.e Monthly exp in column 3 to look(bring) the amount
> from sheet Jan09, amount of 5121 where the dept is 100.
>
> It is clearly visible that 5121 head is present with dept 100,120 & 140 in my
> example
>
> Can anyone please help me on this?
>
> Hope that is clear.
 | 
Pages: 1
Prev: Text Changing to Symbol
Next: not printing correctly