conditional summing from other worksheet

[Guest]

I am creating a budget workbook. The first sheet will display totals broken
down by Type and Date as entered on other sheets. So, I want the following
function displayed:

Drawing from the sheet called "B & R Materials," show the Sum from Column L
for items that have Type "BOOK" in Column C and "Jan-07" in hidden Column M.

I can get it to work if I direct it to one cell on "B & R Materials", but
not to apply that to the whole column. This is the formula that works for one
cell:
=IF(AND('B & R Materials'!C4="Book", 'B & R Materials'!M4="Jan-07"),SUM('B &
R Materials'!L4))

If I change C4 to $C:$C, it doesn't work. Why? What am I doing wrong?
Thanks,
S.

 

[Guest]

You don't need sum when it only one cell in L4

=IF(AND('B & R Materials'!C4="Book", 'B & R Materials'!M4="Jan-07"), 'B & R
Materials'!L4)

If you want a whole column in C
=IF(AND(COUNTIF('B & R Materials'!C:C,"Book"), 'B & R
Materials'!M4="Jan-07"), 'B & R Materials'!L4)

 

[Guest]

Thank you. This looks like the right direction. I do infact want the sum from
column L for all items with Book in Column C and Jan-07 in Column M. So I
modified your suggestion to this:

=IF(AND(COUNTIF('B & R Materials'!C:C,"Book"),'B & R
Materials'!M:M,"Jan-07"), 'B & R Materials'!L:L)

And when I enter the formula, a save window pops up to Update Values: B & R
Materials. I don't understand what that is asking for. (Sorry, this is only
my second real use of Excel.)

Thanks for your help.

 

[Guest]

My fault. Typo in my worksheet name.

So I fixed the typo, and the calculation is working without error, but I've
done something wrong because it returns a value of $0.00.

Here is the formula:
=IF(AND(COUNTIF('B & R Materials'!C:C,"Book"),COUNTIF('B & R
Materials'!M:M,"Jan-07")),'B & R Materials'!L:L)

And here are the values in the first entry in B & R Materials:
C4 = Book
M4 = Jan-07
L4 = $49.95

Any ideas why it returns a sum of 0.00?

Thanks again.
-S