Lookup and Sum Combination

Hello,

I've been trying to figure out how to write this formula for a while now and finally decided I need some help. Here is a very dumbed-down version of the data I'm using and the steps of what I'm trying to accomplish. Any help would be greatly appreciated.

Fruit	State	Cost	Test 1	Test 2
Apple	FL	1.00	True	True
Orange	FL	2.00	False	True
Banana	CA	2.50	True	False
Apple	PA	1.25	False	False
Apple	FL	1.10	True	False

1. I need to lookup values in the cost column based on values in both the Fruit and State columns. (All Apples from FL for example)
2. I need to sum all the values returned from step 1.
3. IF step 2 does not return a certain value, I need to determine if Test 1 and Test 2 are both True/False or one is True and one is False.

I need to keep all of this logic in one cell if possible rather than pulling the lookup values from step 1 into a new array and summing from there.

Not sure if that is clear or not, so please let me know if clarification is needed. Thanks!

Originally Posted by seansmithMMA

3. IF step 2 does not return a certain value, I need to determine if Test 1 and Test 2 are both True/False or one is True and one is False.

What is this "certain value"?

You can use SUMIFS to total Apples in FL. I don't understand Step 3. How do you plan to check the True/False Combination?


Regards, TMS

You're welcome.