Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am currently using the below formula to calculate enablement costs for meters

=SUM(SUMIF($D74,{"New Business"}),$U74*5500)

This worked fine as the fee of $5500 was standard, however I now need to incorporate a new cost for a specific meter. The NZ meter is $13000. In column F the classification is NZ Instant Reserve.

How do I maintain the above formula for the rest of my spreadsheet, but add the additional IF check for the specific meter (which only account for about 10% of my whole list).

share|improve this question
I agree with pnuts - this part of the formula quoted SUMIF($D74,{"New Business"}) always returns zero (no matter what value you have in D74) so your whole formula as written here can be shortened to =U74*5500 - do you expect the result of the formula to change based on what's in D74? It won't. Either you have quoted the wrong formula here or possibly the formula will give you results you don't expect - please explain in words what the formula should do? – barry houdini Mar 21 '13 at 14:13
up vote 1 down vote accepted

As long as it's just two values you can modify the formula along these lines:

=SUM(SUMIF($D74,{"New Business"}),$U74*(IF(F1="NZ Instant Reserve", 13000, 5500)))

You can modify the F values as needed. If there are more values you will need to switch to using a table and the LOOKUP function.

share|improve this answer
Thanks Brad there are just 2 values luckily. – Rohan F Mar 21 '13 at 1:07
-1 because the first part of the formula looks horrible to me and @Arvind has a good point, IMHO. – pnuts Mar 21 '13 at 10:15
Well @pnuts if want to down vote me for apparently answering the OP's question (see his comment) then that's your right. While not the most elegant it seems to satisfy the requirement. I do think that eventually he will need a lookup table. – Brad Patton Mar 21 '13 at 11:55
@Brad I do agree I was a bit harsh but please note I saw my -1 as on offset to a +1 and would not have downvoted otherwise. I admit I have been influenced by and am rather separating votes from ticks. I plan to look at some of your other answers and see if I can’t also ‘offset’ this minus! – pnuts Mar 21 '13 at 12:45
@pnuts np and thanks for the upvote. There are definite times when a detailed and more elegant answer are called for. Other times it seems the OP is just looking for a quick fix to their solution. – Brad Patton Mar 21 '13 at 14:28

Is it possible for you to store the value of "The NZ meter" at some place in another sheet and then pick up the value from there instead of hard-coding it in the formula.

share|improve this answer
I think he's looking for a formula that conditionally chooses between the 2 "NZ" values based on some other requirement. – killermist Mar 21 '13 at 4:00
@killermist It is not very clear without taking into account the OP's other 3 questions (and not even then!), but I think Arvind is correct. Yes, there are only 2 "$" values at present (AUD/NZ) but even without prospect of additional currencies it is probably best to plan for price changes. There is no way I would choose Brad's formula (but have not suggested an alternative since OP has not provided the clarification requested). – pnuts Mar 21 '13 at 10:13
Sorry for the late response but yes i do get other values for new business. The New Business SUMIF check, references new customers who need a meter installed – Rohan F Mar 26 '13 at 23:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.