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="wareh ouse"),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" wrote in message
...
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" 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 <atyahoo<dotcom
----

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

Laddy

"Laddy" wrote in message
...
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" wrote in message
...
"Bob Phillips" 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 <atyahoo<dotcom
----

You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Laddy" wrote in message
...
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 <atyahoo<dotcom
----

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

Laddy

"Laddy" wrote in message
...
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 <atyahoo<dotcom
----

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

Bob

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

Laddy