Calc Driving me insane

[Matthew Howell]

I've got 3 columns, A contains a date, B contains text, C contains figures

I want to sum the contents of certain cells in column C if the criteria in
the adjacent cell is met, eg. column A is greater than a specific date &
column B contains the text "cheese"

i.e.
01/10/2004 bread 20
01/10/2004 cheese 40
01/05/2004 cheese 40

Criteria = Column A <01/09/2004, Column B contains "cheese" = 40

 

[Cutter]

One way:
In D1 type this formula:
=if(and(a1>date,b1="cheese"),c1,"")
copy this down as far as necessary and sum the total in column

 

[RagDyer]

Try this:

=SUMPRODUCT((A1:A100<DATE(2004,1,9))*(B1:B100="cheese")*C1:C100)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I've got 3 columns, A contains a date, B contains text, C contains figures

I want to sum the contents of certain cells in column C if the criteria in
the adjacent cell is met, eg. column A is greater than a specific date &
column B contains the text "cheese"

i.e.
01/10/2004 bread 20
01/10/2004 cheese 40
01/05/2004 cheese 40

Criteria = Column A <01/09/2004, Column B contains "cheese" = 40

 

[RagDyer]

I just realized that in your example you typed;
<"Column A <01/09/2004">
But your wording expressed "column A is greater than a specific date ".

Note: my suggested formula is "less then 1/9/04".
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Try this:

=SUMPRODUCT((A1:A100<DATE(2004,1,9))*(B1:B100="cheese")*C1:C100)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I've got 3 columns, A contains a date, B contains text, C contains figures

I want to sum the contents of certain cells in column C if the criteria in
the adjacent cell is met, eg. column A is greater than a specific date &
column B contains the text "cheese"

i.e.
01/10/2004 bread 20
01/10/2004 cheese 40
01/05/2004 cheese 40

Criteria = Column A <01/09/2004, Column B contains "cheese" = 40