Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Sum with two conditions | Excel Discussion (Misc queries) | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
Sum If with two or more conditions | Excel Worksheet Functions |