SUMIF with 2 criterias

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

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

Laddy

 

[Roni]

Laddy,

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

Note: end the formula with Ctrl + Shift + Enter

Roni

 

[Bob Phillips]

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

 

[Max]

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

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

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

 

[Laddy]

Thanks lads I shall now have a bash at doing the job
Good luck to you all

Laddy

 

[Bob Phillips]

That will teach me to just edit the OPs input rather than re-type

 

[Max]

You're welcome !
Thanks for posting back ..

 

[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>

 

[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

 

[Max]

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.

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

 

[Bob Phillips]

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

Bob