Share via

SUMPRODUCT & INDEX MATCH formula to sum the alternative cells based on previous cell value match.

Anonymous
2024-04-29T16:37:24+00:00

Hello Everyone,

Although I'm new here, the information on this forum has proven very valuable when figuring out the best way to tackle a certain calculation.
Today, however, I've stumbled onto something I can't seem to solve or find help for even with the almighty Google.

I must make the sum of the alternative cells(MDs) in a row if the previous cell value matches the version number I am referring to.

I can make the total sum of the row using the below formula but can't get the sum for v001, v002 & v003 separately. can someone help me with the same?

Total MD's SUM in 3rd ROW = SUMPRODUCT($D3:$Q3,--(MOD(COLUMN($D3:$Q3)-COLUMN($D3),2)=1))

Expected output

3rd ROW Sum,

  • v001 = 3, v002 = 1.25, v003 = 1.25

4th ROW Sum,

  • v001 = 8, v002 = 2.5

5th ROW Sum

  • v001 = 4.5, v002 - 1.75, v003 -1.75

DATA Sample:

SUM 4/22/2024 4/23/2024 4/24/2024 4/25/2024 4/26/2024 4/29/2024 4/30/2024
v001 v002 v003 Version MD's Version MD's Version MD's Version MD's Version MD's Version MD's Version MD's
v001 1 v001 1 v001 1 v002 1 v002 0.25 v003 1 v003 0.25
v001 5 v001 3 v002 1.5 v002 1
v001 1.5 v001 1 v001 1 v001 1 v002 1.75 v003 1 v003 0.75
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich~M 20,370 Reputation points Volunteer Moderator
    2024-04-29T21:27:28+00:00

    Hi. I am an Excel user like you.

    I copied your sample above and pasted it into a spreadsheet and Hans' formula didn't work for me either. The reason I found is that your entries v001, v002, and v003 in A2, B2, and C2 each have an extra space at the end so that Excel did not recognize them as a match. When the extra spaces were removed the formulas worked.

    From your description above it appeared to me that you actually wanted Columns A:C to total by the rows rather than one total for each header. You can do that by entering the ranges by row rather than the entire table. Use this in A3, drag across and then down.

    =SUM(IF($D3:$P3=A$2, $E3:$Q3))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. HansV 462.6K Reputation points
    2024-04-29T18:46:39+00:00

    It works with the sample you provided:

    Of course, you'll have to change the range references if your sheet looks different.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-04-29T17:28:22+00:00

    Hi HansV MVP

    Thanks for your suggestions, this solution does not fulfill my requirements.

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. HansV 462.6K Reputation points
    2024-04-29T16:45:23+00:00

    In A3:

    =SUM(IF($D$3:$P$5=A$2, $E$3:$Q$5))

    If you do not have Microsoft 365 or Office 2021, confirm by pressing Ctrl+Shift+Enter.

    Fill to the right to C3.

    Was this answer helpful?

    0 comments