IF function for multiple cells

[Boomboomsticks]

Hi guys,

Hoping you can help me out, I'm trying to keep track of how much my friends owe me for an event. So I've created a sheet which has the total number of money that I'm owed by all of them.

I have a two cells at the bottom, a 'Total' and a 'Remaining' cell (which currently both are the same number) and a 'Paid' column which if they've paid I put 'No' or 'Yes'. What I would like to do is hopefully create a simple function so that the 'Remaining' cell looks at the Paid column and if there's a 'Yes' in one of the cells then it reduces the number by 15. However, I can only get the cell to reduce by 15 once and not sure how to get it to look at all cells in that column and reduce by 15 for each 'Yes'.

This is the basic function I have: =IF(E4="No",C19-15)
C19 being the 'Remaining' cell.

Any help would be appreciated.

Thanks.

 

[AlanY]

Re: Help with the IF function for multiple cells

you can try to count the number of No's in Column E, and time 15 to take away from the remaining like this

=COUNTIF(E:E,"No")

and

=C19-15*COUNTIF(E:E,"No")

 

[Boomboomsticks]

Re: Help with the IF function for multiple cells

That's brilliant AlanY, that worked perfectly!

Thanks bud!

 

[AlanY]

Re: Help with the IF function for multiple cells

you're welcome