Looking for a more elegant solution to a long equation for adding multiple ranges that meet two criteria..

[Simplemountain]

Consider the following formula:
=IF(
SUM(
IF(N6=AB40,(SUMIFS(S7:AD7,$S$6:$AD$6,X41))),
IF(N8=AB40,(SUMIFS(S9:AD9,$S$8:$AD$8,X41))),
IF(N10=AB40,(SUMIFS(S11:AD11,$S$10:$AD$10,X41))),
IF(N12=AB40,(SUMIFS(S13:AD13,$S$12:$AD$12,X41))))=0,"",

SUM(
IF(N6=AB40,(SUMIFS(S7:AD7,$S$6:$AD$6,X41))),
IF(N8=AB40,(SUMIFS(S9:AD9,$S$8:$AD$8,X41))),
IF(N10=AB40,(SUMIFS(S11:AD11,$S$10:$AD$10,X41))),
IF(N12=AB40,(SUMIFS(S13:AD13,$S$12:$AD$12,X41)))))

For the first line, the formula first evaluates if N6=AB40. If this is true, then it looks at the range S6:AD6 and compares it to the value in X41. If the value anywhere in this range is equal to X41 then it sums the corresponding numerical cell below it in range S7:AD:7

This actually works quite well but the clip I've shown is only a small snippet. There are actually 12 lines of this that repeat and it is quite cumbersome. I'm curious if anyone can suggest a more elegant solution

 

[Legacy 456129]

=SumIfs is what you want to look at or the complex Sumproduct.

 

[Eric W]

Maybe:

=IFERROR(1/(1/(SUMIFS(INDEX(S7:AD13,MATCH(AB40,N6:N12,0),0),INDEX(S6:AD12,MATCH(AB40,N6:N12,0),0),X41))),"")

One assumption is that N7, N9, N11 won't ever match AB40. I also didn't quite understand your use of the $ sign, so I left them off. In limited testing, I got the same results as your formula.

 

[DanteAmor]

Try this array formula

{=SUM(IF((N6:N12=AB40)*(S6:AD12=X41),S7:AD13))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

Or this "regular" formula:

=SUMAPRODUCTO((N6:N12=AB40)*(S6:AD12=X41),S7:AD13)

If the result is 0 and if you want blank instead of 0, you can change the cell format to for example:
0.00; -0.00 ;;

________________________________________________________________

If you don't want to modify the cell format, then it could be this formula array:

{=IF(SUM(IF((N6:N12=AB40)*(S6:AD12=X41),S7:AD13))=0,"",SUM(IF((N6:N12=AB40)*(S6:AD12=X41),S7:AD13)))}

Or "regular" formula
=IF(SUMAPRODUCTO((N6:N12=AB40)*(S6:AD12=X41),S7:AD13)=0,"",SUMAPRODUCTO((N6:N12=AB40)*(S6:AD12=X41),S7:AD13))