software

i have 3 colums columns (date, cash and check)

i like to sum the cash column if the date is eg 07/03/2006 and if check is 1
(1 means that i gave money and 0 that i owe money)

i tried everything
please help me
sorry for my english


If date is column A, cash column B and check column C)

sumproduct((a2:a200=date(2006,3,7))*(c2:c200=1)*(b 2:b200))

ALthough from your date format it is not clear if it is dd/mm/yy or
mm/dd/yy you may need to change the date to date(2006,7,3)

If the date was contained in a sperate cell say d1 the formula would
simplify to
sumproduct((a2:a200=d1)*(c2:c200=1)*(b2:b200))

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=521676A BC
07/03/200613000OK
07/03/20067000
07/03/20068500OK
10/03/200615000OK

i have this table and i write
=SUMPRODUCT(A1:A4= date(2006;03;07));(C1:C4=quot;OKquot;);(B1:B4)
and i get result 0 why?
my result should be 21500

quot;Davquot; wrote:

gt;
gt; If date is column A, cash column B and check column C)
gt;
gt; sumproduct((a2:a200=date(2006,3,7))*(c2:c200=1)*(b 2:b200))
gt;
gt; ALthough from your date format it is not clear if it is dd/mm/yy or
gt; mm/dd/yy you may need to change the date to date(2006,7,3)
gt;
gt; If the date was contained in a sperate cell say d1 the formula would
gt; simplify to
gt; sumproduct((a2:a200=d1)*(c2:c200=1)*(b2:b200))
gt;
gt; Regards
gt;
gt; Dav
gt;
gt;
gt; --
gt; Dav
gt; ------------------------------------------------------------------------
gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; View this thread: www.excelforum.com/showthread...hreadid=521676
gt;
gt;


=SUMPRODUCT((A1:A4= date(2006;03;07))*(C1:C4=quot;OKquot;)*(B1:B4))

The conditions are evaluated as true and false, which is text but if
they are multiplied together they become a value hence the * which you
changed to ;
Also you were missing an opening and closing bracket I have highlighted
all these bits in red.

If you type in the formulan and select a bit of it say
(a1:a4=date(2006;03;07) and press f9 it will show you have it has
evaluted eg true,true false, true then escape will undo. you can do
this to check different bits of the formula if it is not working. It
will be a good test if you have problems with the date.

using the * the following applies to the first 2 conditions
true * true =1
false * false=0
true * false=0
false 8 true= 0

These values multiplied by b1:b4 give you the sum

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=521676Try:
=SUMPRODUCT(--(A1:A4=date(2006;3;7));--(C1:C4=quot;OKquot;);(B1:B4))

If that doesn't work, copy the formula you tried from the formula bar and paste
it into your response.

It's difficult to guess what could go wrong if there may be typos in the formula
just in the post.
yiota wrote:
gt;
gt; A B C
gt; 07/03/2006 13000 OK
gt; 07/03/2006 7000
gt; 07/03/2006 8500 OK
gt; 10/03/2006 15000 OK
gt;
gt; i have this table and i write
gt; =SUMPRODUCT(A1:A4= date(2006;03;07));(C1:C4=quot;OKquot;);(B1:B4)
gt; and i get result 0 why?
gt; my result should be 21500
gt;
gt; quot;Davquot; wrote:
gt;
gt; gt;
gt; gt; If date is column A, cash column B and check column C)
gt; gt;
gt; gt; sumproduct((a2:a200=date(2006,3,7))*(c2:c200=1)*(b 2:b200))
gt; gt;
gt; gt; ALthough from your date format it is not clear if it is dd/mm/yy or
gt; gt; mm/dd/yy you may need to change the date to date(2006,7,3)
gt; gt;
gt; gt; If the date was contained in a sperate cell say d1 the formula would
gt; gt; simplify to
gt; gt; sumproduct((a2:a200=d1)*(c2:c200=1)*(b2:b200))
gt; gt;
gt; gt; Regards
gt; gt;
gt; gt; Dav
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Dav
gt; gt; ------------------------------------------------------------------------
gt; gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=521676
gt; gt;
gt; gt;

--

Dave Peterson

u r the best
it didn't work at the office but it works at home

Thanks!!!!

quot;Dave Petersonquot; wrote:

gt; Try:
gt; =SUMPRODUCT(--(A1:A4=date(2006;3;7));--(C1:C4=quot;OKquot;);(B1:B4))
gt;
gt; If that doesn't work, copy the formula you tried from the formula bar and paste
gt; it into your response.
gt;
gt; It's difficult to guess what could go wrong if there may be typos in the formula
gt; just in the post.
gt;
gt;
gt;
gt; yiota wrote:
gt; gt;
gt; gt; A B C
gt; gt; 07/03/2006 13000 OK
gt; gt; 07/03/2006 7000
gt; gt; 07/03/2006 8500 OK
gt; gt; 10/03/2006 15000 OK
gt; gt;
gt; gt; i have this table and i write
gt; gt; =SUMPRODUCT(A1:A4= date(2006;03;07));(C1:C4=quot;OKquot;);(B1:B4)
gt; gt; and i get result 0 why?
gt; gt; my result should be 21500
gt; gt;
gt; gt; quot;Davquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; If date is column A, cash column B and check column C)
gt; gt; gt;
gt; gt; gt; sumproduct((a2:a200=date(2006,3,7))*(c2:c200=1)*(b 2:b200))
gt; gt; gt;
gt; gt; gt; ALthough from your date format it is not clear if it is dd/mm/yy or
gt; gt; gt; mm/dd/yy you may need to change the date to date(2006,7,3)
gt; gt; gt;
gt; gt; gt; If the date was contained in a sperate cell say d1 the formula would
gt; gt; gt; simplify to
gt; gt; gt; sumproduct((a2:a200=d1)*(c2:c200=1)*(b2:b200))
gt; gt; gt;
gt; gt; gt; Regards
gt; gt; gt;
gt; gt; gt; Dav
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Dav
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=521676
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;