Excel help (conditional formatting and running totals)

[Feneant2]

I’m using Excel 2010 working on charity numbers and looking at doing the following 2 things:

1- I am comparing a row to another row and wish to be able to color code based on if the donation has increased or decreased. When I look at conditional formatting, I can only seem to do so based on a static number, can I somehow have it say ‘If E10 > F10 then blue else red’? I would like this for the entire column and can’t figure it out.

2- I need to provide a total for each weeks’ donation. Can I flag each cell in a column to show for what week is it relevant? That way I don’t need to figure out the weekly calculation myself (that got really hard last week). To make this one more confusing, I need to report for 2 separate groups which are on the same form meaning I need to flag the cell for both week and group… is that something doable?

Thanks in advance!

 

[postmark]

for the conditional formatting, select the cell you want (F10 for instance) and click Conditional Formatting - New Rule.

Then select use formula to determine which cells to format.

In the formula bar enter this: =F10>E10

Then select your format for if that's true or not. You can drag that down the F column to other cells if you want.

I would need to see an example of how your sheet works to answer the 2nd part.

 

[EagleKeeper]

For #2; you will have to have a trigger cell that is used as a filter

 

[Feneant2]

Oops, slow to reply, I have an old Email so wasn't notified of answers!

Thank you postmark, that was very simple for the #1 question

For the 2nd I think a trigger may be the best way but it gets confusing as I was thrown a curve ball and need 2 triggers. I have a column where I identify a department and another for the week.

I then added 2 tables under the list of name, one for each department where both are divided in week 1 through 6 where each cell has a formula that looks at the donation amount and trigger (however it's done) to summarize only the ones that match said week from the donations

I was trying something like:
=IF((B2:B274=SSC and C2:C274=WEEK1),SUM(E2:E274),0)

where I need to look at both B and C to match a criteria for it to make a sum of E... but that formula gives the result of #VALUE!... although in the formula bar it does seem to recognize my logical test as the 2 triggers and the sum as the return of true and 0 as false.

I'm not sure if that would even work though, if it meets the logical, would it sum EVERY row in the column regardless of results or only where it meets though? (I have issues with reasonning so I can't figure out what would happen)

Thanks again

 

[actuarial]

Do you have a column with the names in it, one with the date the contribution was made and then one with the contribution amount?

If so you can use sumproduct() or sumifs() to make your table (I've never used sumifs() before but I know it exists now.

If you change up the data and post a screenshot of your spreadsheet I can type out the formula for you.

 

[Feneant2]

Thanks actuarial, I was coming to post that if was the wrong function, I went with:
=SUMIFS(E2:E274,B2:B274,"SSC",C2:C274,"1")

and it worked like a charm!

Thanks for the reply guys, this will make things a lot easier for me!