Sumif with three intersecting values

[Steve K]

I have a Table with three columns: I.D., Condition, and User Defined Field 4. The table is unsorted, and sorting it would be difficult due to how it is imported. There are multiple entries for each I.D.

On a seperate page, I extract all unique values from each of the three columns.

You can then place check marks next to each unique Condition and unique User Defined Field 4 for them to be considered in the calculations.

The calculations are performed on each unique I.D.

For each I.D., I need to sum up all the values for the all of the checked unique User Defined Field 4 values, but they should be summed only if the respective Condion is one of the checked unique conditions.

I'm having a very hard time describing this without the workbook, so I just attached a scrubbed version for clarity.

On the Calculations tab, under Conditional Events, I was able to get it to sum all of the conditional events if the box under conditional event is checked, however it doesn't take into account Event Count. I need that additional condition.

The way I was doing it was nested if statements, but adding the additional condition would mean I would have up to 20 events and 20 conditional events, so 400 nested if statements for one cell. I'm sure there's a better way.

I wish I could describe this better, unfortunately this is the best I could manage.

Thanks for any help you can give,
Steve

[FDibbins]

not sure if this is the problem or not, but looking at your formula, your 1st if statement has no condition attacted to it?

{=SUM(IF(B$4,IF(ID_LIST=$G4,IF(Condition_List=$A$4,1,0),0)),0.........

however, a sumproduct() may be an easiersimpler approach to take

[FDibbins]

I added some helper columns, and used a series of countif(). let me know if this is what you want?

[Steve K]

In response to your first post,
{=SUM(IF(B$4,IF(ID_LIST=$G4,IF(Condition_List=$A$4,1,0),0)),0.........
There actualy is a condition, it's just not apparent (it's also kind of lazy and probably bad technique). I think (please correct me if I'm wrong) that in the case of boolean logic IF(CELL,Value,Value) is the same as IF(CELL=TRUE,Value,Value). I had the check boxes linked to the respective cell to toggle between true & false, so those would be the only two possible conditions. Teseting like this may not work if the value isn't true Boolean. For instance, if a cell contans TRUE, it will test True in both cases, but if it contains a 1, it will test true in the first, false in the second.

Now, for your second post,

That's an extremely clever solution. It keeps me from having to sort which I really didn't want to have to do and the function will be fairly short. I would have never thought of that.

Thanks for the help!
Steve

[Steve K]

I tried to implement it, and found one shortfall (undoubtely due to my very vague description of my requirements). I'll try to clarify a little.

Each list in the table is an event. So, there are multiple events for each ID. I need to get two numbers for the calculations. One is a selected number of events (taken from the Condition Column, events are selected under Event Count Selection), and one is a conditional number of events (taken from the User Defined Field 4 Column, events are selected under Conditional Count Selection).
I then need to calculate a percentage of the conditional events to the selected events. Note that selected events isn't the same as the total number of events for the table. Some events will be ignored, and need to be excluded from the calculation.

Using your method, I can calculate the Selected Events properly for each ID number. It will count the event only if the appropriate box under Event Count Selection is checked.

The problem I'm having is I need to calculate the Conditional Events only if the Conditional Event box is checked, as well as if the condition for the event is checked as well. I need to exclude the conditional event if the condition for that event is not checked. The solution you proposed only tests the conditional event against ID and User Defined Field 4 column, I need it to test against Condition column as well.

In order to not have 20 nested formulas within 20 formulas, I was hoping to do some fancy vlookup array formula, i.e. match ID and User Defined Field 4, then lookup Condition and see if it is checked. Unfortunately I couldn't get this to work.

So, Selected Events is where two items intersect, ID and Condion
Conditional Events is where three items intersect, ID, User Defined Field 4, and Condition.

Thanks again for the help, let me know if I need to clarify further (not sure if I really clarified anything here).