sumif function

[yiota]

i have 3 columns (date, cash and check)

i would like to sum cash if date is eg 07/03/2006 and check is Ok

sorry for my english
thank you

 

[Dave Peterson]

=sumproduct(--(a1:a10=date(2006,3,7)),--(b1:b10="ok"),(c1:c10))

Adjust the range2 to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Ps. I checked for March 7, 2006.

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A20=--"2006-03-07"),--(C2:C20="Ok"),B2:B20)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[CLR]

Nice explanation Dave......

=SUMPRODUCT(--(A1:A10=F1),--(B1:B10="ok"),(C1:C10))

This mod to your formula will allow the OP to put the date in cell F1 and
change it there without having to change it in the formula if desired.

Vaya con Dios,
Chuck, CABGx3

 

[yiota]

thank you all but i can't make it work

=SUMPRODUCT(--(A2:A20=--"2006-03-07"),--(C2:C20="Ok"),B2:B20)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Duke Carey]

What do you mean by you "can't make it work"? Do you get an error? Are you
sure the dates are actually dates? Is "OK" the correct value to be looking
foor in the check column?

A little help from you allows us to better help you.