SUM if the conditions are met
 

I have the following case

A B C
1 Date Value Mark
2 23.10.2007 100 X
3 23.10.2007 150
4 23.10.2007 200
5 23.10.2007 300 X
6 24.10.2007 123
7 24.10.2007 344
etc...

Now I would like to have a formula which would sum the values on a
certain date, that are marked with an X. For example, the result on
23.10.2007 would sum only 100+300=400

SUM if the conditions are met
 

In D2:
=SUMPRODUCT((A2:A10=--"23 Oct 2007")*(C2:C10="X"),B2:B10)
Adapt the ranges & criteria to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mortir" wrote:
I have the following case

A B C
1 Date Value Mark
2 23.10.2007 100 X
3 23.10.2007 150
4 23.10.2007 200
5 23.10.2007 300 X
6 24.10.2007 123
7 24.10.2007 344
etc...

Now I would like to have a formula which would sum the values on a
certain date, that are marked with an X. For example, the result on
23.10.2007 would sum only 100+300=400

SUM if the conditions are met
 

=SUMPRODUCT(--(A2:A7=DATE(2007,10,23)),--(C2:C7="X"),(B2:B7))

--
Gary''s Student - gsnu2007a


"Mortir" wrote:

I have the following case

A B C
1 Date Value Mark
2 23.10.2007 100 X
3 23.10.2007 150
4 23.10.2007 200
5 23.10.2007 300 X
6 24.10.2007 123
7 24.10.2007 344
etc...

Now I would like to have a formula which would sum the values on a
certain date, that are marked with an X. For example, the result on
23.10.2007 would sum only 100+300=400

SUM if the conditions are met
 

"Max" wrote in message
...
In D2:
=SUMPRODUCT((A2:A10=--"23 Oct 2007")*(C2:C10="X"),B2:B10)

How would that work when his data uses "10" for October?

SUM if the conditions are met
 

In D2:
=SUMPRODUCT((A2:A10=--"23 Oct 2007")*(C2:C10="X"),B2:B10)

How would that work when his data uses "10" for October?

It'll work fine as long as the dates in col A are real dates (presumed). The
double minus in: --"23 Oct 2007" converts the text string to a real,
specific date, as per OP's specs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

SUM if the conditions are met
 

Do an experiment.

In A1 enter the date 1/2/2007 (or 2/1/2007 if you use US date format)
In B1 enter =A1=--("1 feb 2007")
The result is TRUE

But
A1 enter the date 1/2/2007 (or 2/1/2007 if you use US date format)
In B1 enter =A1=--("1 2 2007") {or A1=--("2 1 2007") in USA}
The result is #VALUE!

But adding acceptably date separator characters, as in
=A1=--("1/2/2007") or =A1=--("1-2-2007")
you again get TRUE
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Max" wrote in message
...
In D2:
=SUMPRODUCT((A2:A10=--"23 Oct 2007")*(C2:C10="X"),B2:B10)

How would that work when his data uses "10" for October?

It'll work fine as long as the dates in col A are real dates (presumed).
The double minus in: --"23 Oct 2007" converts the text string to a real,
specific date, as per OP's specs.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---