sumif function
 

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

sumif function
 

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: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=521676

sumif function
 

A B C
07/03/2006 13000 OK
07/03/2006 7000
07/03/2006 8500 OK
10/03/2006 15000 OK

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

"Dav" wrote:


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: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=521676

sumif function
 

=SUMPRODUCT((A1:A4= date(2006;03;07))*(C1:C4="OK")*(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: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=521676

sumif function
 

Try:
=SUMPRODUCT(--(A1:A4=date(2006;3;7));--(C1:C4="OK");(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:

A B C
07/03/2006 13000 OK
07/03/2006 7000
07/03/2006 8500 OK
10/03/2006 15000 OK

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

"Dav" wrote:


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: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=521676



--

Dave Peterson

sumif function
 

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

Thanks!!!!

"Dave Peterson" wrote:

Try:
=SUMPRODUCT(--(A1:A4=date(2006;3;7));--(C1:C4="OK");(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:

A B C
07/03/2006 13000 OK
07/03/2006 7000
07/03/2006 8500 OK
10/03/2006 15000 OK

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

"Dav" wrote:


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: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=521676



--

Dave Peterson