SOLVED

# Need a formula

Copper Contributor

# Need a formula

I need a formula for the following

PM =8 and N =8 this needs to be calculated then taken off the value of 70

How do i do this?

5 Replies
best response confirmed by Redhs01 (Copper Contributor)
Solution

# Re: Need a formula

@Redhs01 There's a lot left to the imagination here, but one possibility could be:

``=value - SUM(COUNTIF(range, {"PM","N"})) * 8``

For example, if the value 70 is in cell A1 and the range to evaluate is B1:N1, the formula would be:

``=A1 - SUM(COUNTIF(B1:N1, {"PM","N"})) * 8``

# Re: Need a formula

Thank you but it doesn't add up correctly, it has the total as 70 when it should be 30 as there are 5 PM or N which being 8 each = 40. Sorry what am i doing wrong??

# Re: Need a formula

Also if I take out the =A1- the total comes out at 0.....that isn't correct i would have thought it would come out at 40??

# Re: Need a formula

All good I was missing a ".....thank you for your help much appreciated

# Re: Need a formula

@Redhs01 You're welcome. I'm glad you were able to work it out. :)

1 best response

Accepted Solutions
best response confirmed by Redhs01 (Copper Contributor)
Solution

# Re: Need a formula

@Redhs01 There's a lot left to the imagination here, but one possibility could be:

``=value - SUM(COUNTIF(range, {"PM","N"})) * 8``

For example, if the value 70 is in cell A1 and the range to evaluate is B1:N1, the formula would be:

``=A1 - SUM(COUNTIF(B1:N1, {"PM","N"})) * 8``