Here's what i'm trying to do with no luck even with scouring the internet:
Column A B C D E F
row 1 20 25 30 40 45 50
row 2 A B C A B N/A
row 3 B B C B B C
row 4 A B C A C A
I'm trying to find all instances where I have an "A" @ 20 and also an "N/A" @ 50, now i can do that using a simple sumproduct with something like:
=SUMPRODUCT((A2:A4="A")*(F2:F4="N/A))
= 1
However i need to make this formula dynamic for analysis purposes. What if i wanted to see an "A" @ 25 and also an "A" @ 45? I don't want to manually go into the formula and change the ranges, i need the formula to be able to adjust dynamically based on inputs.
So essentially inputs would be in a different area and the sumproduct would return the result based on the inputs changing. My problem is i don't know how to get sumproduct to use two horizontal conditions & two vertical conditions at once. In this example i need something to do this:
=SUMPRODUCT((A1:F1=20)*(A2:F2="A") AND SUMPRODUCT((A1:F1=50)*(A2:F2="N/A")
How do i get this all combined into 1 neat formula that works?
Thanks so much!
Bookmarks