Help with Formula for COUNTIIFS INDEX MATCH Multiple Criteria Same Column

Apologies for the late reply and thank you both for your input, it is greatly appreciated.

Both examples achieved what I needed but since the references are not fixed, I opted to go with the solution by HansDouwe. Also Hans, you were correct about the error. Thank you for finding that as well.

I do have an additional question/update. I moved the data to a separate tab and updated the formula. I added columns to the Example tab which breaks down the count for each header and value separately.

Hans, I need the formula you created to be updated to drill down to get the specific count for each header and value.

An updated spreadsheet is attached with examples.

Thank you again for all your help. It is greatly appreciated.

This is exactly what I needed! Thank you again! One more thing though, could you update the formula for the overall PnL? I am unable to adjust the new formula to make it work. Thank you.

That works perfectly! Thank you again!

Hello,
I have a COUNTIFS INDEX MATCH formula that matches the header in a column and then counts the number of times a certain value appears in that column. I was able to get help here previously with changes and additions. I am hoping someone can help me with more updates and changes.

Previous post:

Please Login or Register  to view this content.

Thank you in advance.

Hello,
I received help with this problem before and the solution worked perfectly but I am reopening this thread to receive additional help with revising and updating the formula.

Below is the main formula used that I would like edited to fit my current needs. The formula is on sheet 'Example(2)'!E2 and filters for the data in the sheet 'Data(2)'! based upon the entries in the table on sheet 'Example(2)'A1:F14.

The formula in 'Example(2)'!E2 creates a table that gives the count based upon the criteria in C2, C3, the total of both criteria, and there percentages of the totals.

I want to keep the general framework of the formula with an additional criterion to be added based upon which cell the formula is in. Making some adjustments to the formula to breakdown more data in a second table 'Example(2)'!H1:K14

Framework I want to keep:

Please Login or Register  to view this content.

Formulas Needed:
I4: The SUM amount of column 'Data(2)'!CS of values where Criteria 1 and 2 are met.
Criteria 1: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)
Criteria 2: Filter for rows where 'Data(2)'!CQ = "Profit"

I5: The SUM amount of column 'Data(2)'!CS of values where Criteria 1 and 2 are met.
Criteria 1: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)
Criteria 2: Filter for rows where 'Data(2)'!CQ = "Loss"

I7: The MIN value from column 'Data(2)'!CO where Criteria 1 and 2 are met.
Criteria 1: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)
Criteria 2: Filter for rows where 'Data(2)'!CQ = "Profit"

K3: The AVG number from column 'Data(2)'!CR where Criteria 1 is met.
Criteria 1: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)

K4: The AVG number from column 'Data(2)'!CR where Criteria 1 and 2 are met.
Criteria 1: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)
Criteria 2: Filter for rows where 'Data(2)'!CQ = "Profit"

K5: The AVG number from column 'Data(2)'!CR where Criteria 1 and 2 are met.
Criteria 1: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)
Criteria 2: Filter for rows where 'Data(2)'!CQ = "Loss"

K8: The MAX number of times the value "Profit" appears "consecutively" in column 'Data(2)'!CQ
Criteria: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)

K9: The MIN number of times the value "Loss" appears "consecutively" in column 'Data(2)'!CQ
Criteria: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above)

K10: The MAX value in column 'Data(2)'!CN
Criteria: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above). All values. Both "profit" and "loss".

K11: The MIN value in column 'Data(2)'!CO
Criteria: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above). All values. Both "profit" and "loss".

K14: The AVG number from column 'Data(2)'!CG
Criteria: Using the same framework and criteria as the formula in 'Example(2)'!E2 (see above). All values. Both "profit" and "loss".

See the spreadsheet attached for example.

I appreciate your responses.