Need help with applying conditional formulas!

Hi all,

I have a real simple (and possibly dumb) question. Why doesn't my sumproduct return the expected value?

Here's my formula (in G83):

=SUMPRODUCT(--(E83="AD")*(D83+G12),--(E83="dscvry")*(D83+F83),--(E83="CD")*(D83+G13),--(E83="AD-B")*(D83+H13),--(E83="receipt")*(D83+F83),--(E83="CD-B")*(D83+H13),--(E83="SCD")*(D83+G13))

Here's what my table looks like:

Ref / ! / Item / Days / Label / Contingent Date / Projected Date / Completed Date
H4 / 1 / Table Delivery / 5 / receipt / 12-5-2016 / [formula] 12-10-2016 / ---- [manual entry later]


The cells I'm adding together are dates and numbers of days to come up with a new date. For example, D83 is "5" days and F83 is "12/5/2016", which should give me "12/10/2016" in G83. What I'm trying to do is have SUMPRODUCT check the "LABEL" of the row it's in (83 in this example) for a matching text entry and return a corresponding formula. So, if E83 has "receipt", sumproduct would return the sum of "D83+F83" and zero from the rest. The label field could also contain CD, AD, SCD, etc. Each one of these "labels" has it's own formula.

The problem is, the result of my formula is 0. Sumproduct isn't multiplying them, is it? My understanding is it's 0+0+0+0+0+0+23446+0+0, and not 0*0*0*0*0*57423*0*0. By the way, I don't need to use sumproduct, but this seems like a simple task and I'm stuck.

Originally Posted by Tony Valko

In "plain English" your formula is saying:

If E83="AD" AND E83="dscvry" AND E83="CD" AND E83="AD-B" AND E83="receipt" AND E83="CD-B" AND E83="SCD" ...

The problem is that E83 CAN'T meet all those conditions at the same time!

Great response Tony. How do I state the same thing, but with an "or"?

Originally Posted by Richard Buttrey

=IF(OR(E83="AD",E83="dscvry",E83="CD",......),this,that)

perhaps.

But why not upload the workbook. There may be easier ways of achieving what you want

Sorry Richard. Was just having noob issues getting the worksheet online. - Cheers

Originally Posted by Tony Valko

I didn't download your file.


The formula looks like it can be written as a nested IF formula as you're testing E83 for various contents and each variable has its own result.

The results are all D83+some other cell.

If none of the conditions are met what result do you expect?

If none of the conditions are met, then I'd like it to return the text "NA".

Originally Posted by Richard Buttrey

Please clarify the reference to D83. The workbook you've uploaded only goes down to row 21.

Also please manually add rge results you expect to see in column G in say column I so that we can understand the end goal. Explain in some notes how yuo've calculated the results.

Richard, my original post referred to cells in my actual workbook. The sample sheet I uploaded does not have the same number of rows and the data is different as well. Please see the highlighted notes on the worksheet itself.

Subject formula is in cell G21. The operation should be that the formula looks at E21 and understands what to do with D21. In some cases, G21 will add D21 to F21. In other cases, D21 is added to G2, H3 or H4.

All that being said, I'm not so concerned with the operations after G21 figures out what to do. I just figured there was an easy way to set up a formula in G21 to check E21 and spit out different formulas depending on what it finds.

Thanks.

Originally Posted by Tony Valko

OK, this "emulates" the SUMPRODUCT function:

=IF(E83="AD",D83+G12,IF(OR(E83={"dscvry","receipt"}),D83+F83,IF(OR(E83={"CD","SCD"}),D83+G13,IF(OR(E83={"AD-B","CD-B"}),D83+H13,"NA"))))

Whether that's what you actually need/want is another story!

Functionally, that accurately works (in my actual workbook). I'm a bit stumped on why there isn't a less cumbersome way to accomplish this tho. Any thoughts?

Originally Posted by Tony Valko

If you create a small 2 column table then you can use a more compact formula.

You would put the text variable in the left column and its corresponding numeric value in the right column (in this case, a formula). For example:

Data Range

	A	B
1	AD	=D83+G12
2	dscvry	=D83+F83
3	receipt	=D83+F83
4	CD	=D83+G13
5	SCD	=D83+G13
6	AD-B	=D83+H13
7	CD-B	=D83+H13

Then, the formula to replace the nested IF would be:

=IFERROR(VLOOKUP(E83,A1:B7,2,0),"NA")

Hi Tony,

If I filled this formula down, would the "key" follow and return D84 when checking E84?

It's totally fine if I need to go with the original formula you posted.