SUMIF with 2 criterias

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

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

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


--
HTH

Bob Phillips

"Laddy" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

"Bob Phillips" <[email protected]> wrote
> =SUMPRODUCT(--(Sheet2!B:B="Warehouse"),--(Sheet2!C:C='2005
> Takings'!A1),Sheet2!D:D)

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)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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

Laddy

"Laddy" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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


"Max" <[email protected]> wrote in message
news:[email protected]...
> "Bob Phillips" <[email protected]> wrote
> > =SUMPRODUCT(--(Sheet2!B:B="Warehouse"),--(Sheet2!C:C='2005
> > Takings'!A1),Sheet2!D:D)
>
> 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)
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Laddy" <[email protected]> wrote in message
news:[email protected]...
> Thanks lads I shall now have a bash at doing the job
> Good luck to you all
>
> Laddy

> That will teach me to just edit the OPs input rather than re-type :-)
... I got "numb" when I tried your earlier response <bg>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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" <[email protected]> wrote in message
news:[email protected]...
> You're welcome !
> Thanks for posting back ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Laddy" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks lads I shall now have a bash at doing the job
>> Good luck to you all
>>
>> Laddy
>
>

"Laddy" <[email protected]> wrote in message
news:[email protected]...
> 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

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

Bob

"Laddy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > You're welcome !
> > Thanks for posting back ..
> > --
> > Rgds
> > Max
> > xl 97
> > ---
> > GMT+8, 1° 22' N 103° 45' E
> > xdemechanik <at>yahoo<dot>com
> > ----
> > "Laddy" <[email protected]> wrote in message
> > news:[email protected]...
> >> Thanks lads I shall now have a bash at doing the job
> >> Good luck to you all
> >>
> >> Laddy
> >
> >
>
>