Hi everyone, I am new to this forum and hoping to get some input.
I have a large table of data and I am using a dynamic sumproduct formula (integrating the offset function) to calculate the total instances that meet two criteria for a specific category.
Current calculation: total instances for Category 1 that meet the below two criteria
Search Criteria: Variable 1: "X" / Test 1: "A"
Formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET($B$11:$B$16,ROW($B$11:$B$16)-MIN(ROW($B$11:$B$16)),,1)),(--EXACT(D5,$B$11:$B$16)),(--EXACT($C$6,$C$11:$C$16))+0)
Answer = 3
Problem: I am slowly building out a second set of test results and I am looking to report on the latest data by migrating to Test 2 results as they become available. Therefore, I need to adjust the formula above to test a third variable in the formula but with an additional condition. This condition needs to first search the Test 2 data range for the variable “A”. If it finds different values, those rows will not be counted. If it identifies blank/empty cells, the formula needs to alternatively search in the Test 1 data range for “A”.
Answer = 3
Note: I have been playing with ISBLANK/AND functions but can’t seem to make anything work.
Any assistance is greatly appreciated 😊
KJ
Bookmarks