Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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 |
#4
|
|||
|
|||
"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 ---- |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 ---- |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 ---- |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
"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 ---- |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
Sumif or Sumproduct 2 criterias not working | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |