0

I searched for similar problems and found some, but I can't seem to get this to work.

I have 

    =SUM(SUMIFS(TotalAmount, Date, "<="&A274, OfferCode, {"REN- xyz -cc","REN- xyz -plg"}, UpgradeCode, {"upgrade - cc","upgrade -plg"}))

Where:

TotalAmount is a named range for two columns that I have manually checked to make sure only have numerical values,

Date is a named range that only contains dates (entered as dates, not strings),

and both OfferCode and UpgradeCode are named ranges containing internal use codes for types of sales.

When I found Dominic's question here I thought my problems were solved, but I must still be doing something wrong.

What I entered returns the error code #VALUE!, which I don't understand because I'm 110% positive that the named range TotalAmount only contains numerical values for sales.

Any help would be tremendously appreciated.

Community
  • 1
  • 1
  • If you use arrays like that, click into the formula bar and hit F9. This will show you each individual array value before it collapses them all into a single result. It could be that one of your versions is giving you a problem that you could then identify and solve. Alternatively, go to the Formulas Ribbon ->Formula Auditing -> evaluate formula to walk through what it does step by step. – Grade 'Eh' Bacon Jul 23 '15 at 20:06
  • Unless you post a data sample, nobody will be able to see if your problem is caused by range sizes. Post a sample file or at least a screenshot on a file sharing site and post the link here. Also, please put in plain words the business logic for the result. It is not clear if your result is supposed to be a match for OfferCode AND UpgradeCode, or if you need an OR instead. – teylyn Jul 23 '15 at 22:05

1 Answers1

0

You could use SumProduct instead of Sumifs along these lines:

 =sumprodcut(TotalAmount*(Date<=A274)*((OfferCode="REN- xyz -cc")+(OfferCode="REN- xyz -plg"))*((UpgradeCode="upgrade - cc")+(UpgradeCode="upgrade -plg")))

If that does not work, share a data sample with your data structure.

teylyn
  • 34,374
  • 4
  • 53
  • 73