Excel help – match data in column – add non-contiguous row data for matching column

by
Column A Column B Column C Column D
Cell 1 Cell 2 Cell 3 Cell 4
Cell 5 Cell 6 Cell 7 Cell 8

Goal is to do two things:

  1. Match a specific item in Column A
  2. Add only Column B and D (not C); return value

So I’m looking for : If A=”data” then add row (cell) data from Column B and D.

I was using Sumif fine up until the point that I needed to add other data that is not contiguous. If it was I’d just be able to use B1:D2 to get all cell 2,3,4,6,7,8 data.

In this situation, I want to get a total of Cell 2 + Cell 4 only if Cell 1 matches what I’m looking for.

This should be recursive through the whole dataset. So if the row matches, add the column values, and then move on to the next one, if match, add column values, until there is a total value for the whole dataset.

TIA

Searched high and low but most examples use contiguous data.

  • Can you post few more examples with expected output, so it is easy to replicate.

    – 

  • Here’s a link to a XLS with sample data: dropbox.com/scl/fi/yhmq4s8z0d24y908bawgf/… Attempting to find totals (regular pay and separately overtime) for all departments. As some of the data is not needed, like hours worked, I can’t just add the whole row. I need to add just the columns with “money” figures in them.

    – 




  • Let me post this as an answer however i wasnt able to locate Non-Exempt Overtime Earnings

    – 

Try any one of the following:

enter image description here

• Formula used in cell D11

=IFERROR(SUM(INDEX($A$2:$BP$8,,XMATCH(A11,$1:$1))),"")

Or,

• Formula used in cell E11

=SUM(TOCOL((A11=$1:$1)*$A$2:$BP$8,3))

Or,

• Formula used in cell F11

=SUMPRODUCT(IFERROR((A11=$1:$1)*$A$2:$BP$8,0))

Or, Single Dynamic Array Formula:

• Formula used in cell G11

=MAP(A11:A20,LAMBDA(α,SUM(TOCOL((α=$1:$1)*$A$2:$BP$8,3))))

Excel can be downloaded from here

Leave a Comment