Conditional Sum

[Troy]

I'm trying to accomplish a great task, but I don't even
know if it is possible. In 1 workbook I have 2 sheets.
Sheet1 is "Totals" and Sheet2 is "Info" (for the totals).
On the "Info" sheet I will be pasting daily reports so
this sheet will get very long. If the Date column
in "Totals" matches the Date column in "Info" then I want
a total in a specific range with the condition that, total
on the rows that have a code "F"

 

[Biff]

Hi Troy,

Yes, it's possible, but you need to be more specific as to
what values you want *summed* and what "code F" means.

Or do you want a *count* of matching dates and "code F"?

Biff

 

[Guest]

OK here are specifics.

If sheet "Total" A2 is equal to sheet "Info" B3 then sum
range M6:M46 on "Info" sheet if F6:F46 = "F"

F means Film Change

This is just for one day and I will be needing to track
YTD. So the range to sum from day to day will change, I
really don't want to create new formulas for every day. I
would like to copy and paste.

I hope with this you can help me out.

 

[Biff]

OK, but that's a little different from the original post.
No problem.

I'm assumming that this formula is in the INFO sheet so
the SUMPRODUCT references don't include a sheet name.

=IF(Total!A2=Info!B3,SUMPRODUCT((F6:F46="F")*M6:M46),0)

Biff

 

[Troy]

Are you an Excel Programmer or what?
You really know your stuff. It worked so far. I need to do
alot more of these formulas but one thing I really wanted
to do is copy and paste. Let me explain, this is the
actual formula I used.

=IF(A8='Info'!C1,SUMPRODUCT(('Info'!F7:F69="F")*'Info'!
K7:K69),0)

after this I wanted to copy and paste and get this formula:

=IF(A10='Info'!C70,SUMPRODUCT(('Info'!F78:F138="F")*'Info'!
K78:K138),0)

but I got this one:

=IF(A10='Info'!C3,SUMPRODUCT(('Info'!F9:F71="F")*'Info'!
K9:K71),0)

Can you tell me of a way to get my desired formula with
having to write in each formula. I need to do a full years
worth.

 

[Biff]

Since your copying to non consecutive cells and the
references are not absolute the ranges adjust accordingly.
XL thinks it's helping you out! That's just one of those
things you have put up with.

Biff

 

[Troy]

Thank you, Biff.

I hid the cells that corresponded to the second sheet and
this did allow me to copy and paste. Thanks to your help
this workbook is looking nice.