0

I have what seems to be a simple issue but I can't resolve it.

I have two columns. One will only ever be a value of 5,10,20,40,50,60,80,signed.

The other is just a currency value.

I've been looking into arrays to see if I can simplify this formula but I'm failing.

Right now, I need the formula to evaluate if the first column is a number and is greater than or equal to 40 but less than 80. If it is, it needs to multiply against the currency value next to it and return the total of the entire set of entries for which this is true.

Can someone help me formulate this as I just end up with a massive sum if. Surely there's a better way?

Improve this question

1 Answer 1

Reset to default
2

You want to use SUMPRODUCT:

=SUMPRODUCT(($A$1:$A$8>=40)*($A$1:$A$8<80),$A$1:$A$8,$B$1:$B$8)

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thanks - I should mention that 'signed' is a part of the first column. Does this affect output? I guess the formula could be extended to translate signed into '100'
Yes, it matters. This formula will fail if there is text in the referenced values. @JohnHalstead.
@JohnHalstead see edit. You may need to refresh the screen
So, final question : what are the asterisks doing here. In English, this translates to:
Re "This formula will fail if there is text in the referenced values", this is only because the 'product' version of SUMPRODUCT is being used here. The native, comma-separated-argument version handles text: =SUMPRODUCT(0+($A$1:$A$7>=40),0+($A$1:$A$7<80),$A$1:$A$7,$B$1:$B$7)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.