SOLVED

# Sum formula across sheets with matching column

Copper Contributor

# Sum formula across sheets with matching column

Platform: Excel 365
I need Columns A
, B, C and E to match in both October and November sheets. If they match, then October column L is added to November column J, the result is shown in November column L. If there is no match, November column J = November column L.

I've attached screen shot of a mocked up template, I can't share the real one due to it being a work document with protected info. But the months cover the span of the year and as clients get added to or taken away from the list, I need a way to accurately, and automatically calculate the year-to-date total spent.

Thank you! This is well beyond my Excel skills

2 Replies
best response confirmed by Quentillian (Copper Contributor)
Solution

# Re: Sum formula across sheets with matching column

In L2:

=J2+IFERROR(INDEX(October!\$L\$2:\$L\$100, XMATCH(1, (October!\$A\$2:\$A\$100=A2)*(October!\$B\$2:\$B\$100=B2)*(October!\$C\$2:\$C\$100=C2)*(October!\$E\$2:\$E\$100=E2))), 0)

Adjust the ranges if the October data extend below row 100.

Fill down.

# Re: Sum formula across sheets with matching column

@HansVogelaarTHANK YOU! That appears to be working just like intended. Thak you so much!

1 best response

Accepted Solutions
best response confirmed by Quentillian (Copper Contributor)
Solution

# Re: Sum formula across sheets with matching column

In L2:

=J2+IFERROR(INDEX(October!\$L\$2:\$L\$100, XMATCH(1, (October!\$A\$2:\$A\$100=A2)*(October!\$B\$2:\$B\$100=B2)*(October!\$C\$2:\$C\$100=C2)*(October!\$E\$2:\$E\$100=E2))), 0)

Adjust the ranges if the October data extend below row 100.

Fill down.