SUMIF with 2 criterias

L

Laddy

Hello,
I am trying to work out how to extract data with 2 criteria using SUMIF and
IF but cannot manage it.

In the worksheet which holds the results:
Column A has dates
Column B where the results of the formula will be placed

I wish to extract data from another worksheet but with an extra criterion
and that is "Warehouse".
This data is taken from "sheet2"
Column B (Where "Warehouse" is one of many different values)
Column C (Where the dates are situated)
Column D (Where the cash is situated)

I tried :

=IF(Sheet2!B:B="Warehouse",SUMIF(Sheet2!C:C,'2005 Takings'!A1,Sheet2!D:D))

It doesn't work (Returns FALSE)..................... any ideas please

Laddy
 
R

Roni

Laddy,

Try to use sumif with array function:
=sum(if((sheet2!C1:C100=B1)*(sheet2!B1:B100="warehouse"),sheet2!D1:D100)

Note: end the formula with Ctrl + Shift + Enter

Roni
 
B

Bob Phillips

=SUMPRODUCT(--(Sheet2!B:B="Warehouse"),--(Sheet2!C:C='2005
Takings'!A1),Sheet2!D:D)
 
M

Max

Bob Phillips said:
=SUMPRODUCT(--(Sheet2!B:B="Warehouse"),--(Sheet2!C:C='2005
Takings'!A1),Sheet2!D:D)
Click to expand...

Think Bob probably meant something like:
=SUMPRODUCT(--(Sheet2!B1:B100="Warehouse"),--(Sheet2!C1:C100='2005
Takings'!A1),Sheet2!D1:D100)

Adapt the ranges to suit ..
(Note: you can't use entire col refs in SUMPRODUCT)
 
M

Max

That will teach me to just edit the OPs input rather than re-type :)
... I got "numb" when I tried your earlier response <bg>
 
L

Laddy

Just to say that the suggested solution work well and I have adapted it to
suit my needs after understanding how it works.
This form of SUMPRODUCT is not in my Excel 97 guide so I must presume it
will be in a later publication. I shall now try to find out.

Thanks

Laddy
 
M

Max

Laddy said:
Just to say that the suggested solution work well and I have adapted it to
suit my needs after understanding how it works.
This form of SUMPRODUCT is not in my Excel 97 guide so I must presume it
will be in a later publication. I shall now try to find out.
Click to expand...

Good to hear that !

Suggest you try Bob Phillips' page for a nice, comprehensive treatment
on SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
B

Bob Phillips

Look in vain, you won't find it, it is an evolution driven by experienced
Excel users.

Bob
 
Top