Sum column entries based on referenced values in another cell

[crumber_nuncher]

Hello all,

Apologies in advance because I've tried searching for this, but I'm running into issues because I'm not really sure what formula I should be using.

I think the command I'm hoping to use is DSUM, but I can't get it to work so I'm not sure if I'm messing it up or barking up the wrong tree? I have syntax issues on a good day, haha.

I have Data in Sheet1. 50+ columns, 2000+ rows, formatted as table, yada yada.
In Sheet2, I'm wanting to return or sum only certain cells from one of the columns, but based off another cell in another column.

Sheet1 (data is formatted as a table: "Data_All")​ Serial Customer Agreement Meter Reading ABC12345678 Completely Fake Company, LLP 110458 001 43524 DEF90123456 Completely Fake Company, LLP 110458 002 213 GHI78998765 Completely Fake Company, LLP 110458 008 6644 RND0254684 Completely Fake Company, LLP 095687 004 5562 ZZZ00025469 Completely Fake Company, LLP 095687 003 22863 ABC09876543 Next Entity in List 152000 187 27 <tbody> </tbody> I'm needing to tally [Reading] from 'Sheet1' based on [Agreement], without specifically referencing the cells (because they are not usually in the neat order you see here).
Sheet2​ Agreement Customer Tally 110458 Completely Fake Company, LLP x 095687 Completely Fake Company, LLP x 152000 Next Entity in List x <tbody> </tbody>

<tbody>
</tbody>

Example: x on first line would = 43524+213+6644 = 50381

---

Here's what I'm trying in Sheet2:
=DSUM(Data_All,Data_All[Reading],MATCH(A2,Data_All[Agreement]))

Evaluation fails at italicized section:
=DSUM('Sheet1'!$A2:BO$2000,'Sheet1'!$T$2:$T$2000,MATCH("110458",'Sheet1'!$AW$2:$AW$2000))

Obviously I have more columns at play here based on the evaluation returns, but it looks like it's so close to properly referencing that it's driving me nuts. Is what I'm trying to do even possible? Is it me and syntax again, or is there a better command to use?

 

[dreid1011]

This would work if you're only matching the criteria of the Agreement #. Adjust ranges to suit your data.

=SUMIF(Sheet6!$A$1:$A$6,"="&A1,Sheet6!$B$1:$B$6)

Sheet6 = your data table sheet, A1:A6 is your reading column on data sheet, A1 is agreement cell on summary sheet, B1:B6 is your agreement column on data sheet. Paste in first cell in your tally column and copy down.

If you need to match more criteria, this can be adjusted.

 

[jarjarbingie]

=SUMIF('Sheet1'!$AW$2:$AW$2000, "110458",'Sheet1'!$T$2:$T$2000)

if AW contains Agreement and T contains Reading, change "110458" to 110458 if Agreement is a number

Hello all,

Apologies in advance because I've tried searching for this, but I'm running into issues because I'm not really sure what formula I should be using.

I think the command I'm hoping to use is DSUM, but I can't get it to work so I'm not sure if I'm messing it up or barking up the wrong tree? I have syntax issues on a good day, haha.

I have Data in Sheet1. 50+ columns, 2000+ rows, formatted as table, yada yada.
In Sheet2, I'm wanting to return or sum only certain cells from one of the columns, but based off another cell in another column.

Sheet1 (data is formatted as a table: "Data_All")​ Serial Customer Agreement Meter Reading ABC12345678 Completely Fake Company, LLP 110458 001 43524 DEF90123456 Completely Fake Company, LLP 110458 002 213 GHI78998765 Completely Fake Company, LLP 110458 008 6644 RND0254684 Completely Fake Company, LLP 095687 004 5562 ZZZ00025469 Completely Fake Company, LLP 095687 003 22863 ABC09876543 Next Entity in List 152000 187 27 <tbody> </tbody> I'm needing to tally [Reading] from 'Sheet1' based on [Agreement], without specifically referencing the cells (because they are not usually in the neat order you see here).
Sheet2​ Agreement Customer Tally 110458 Completely Fake Company, LLP x 095687 Completely Fake Company, LLP x 152000 Next Entity in List x <tbody> </tbody>

<tbody>
</tbody>

Example: x on first line would = 43524+213+6644 = 50381

---

Here's what I'm trying in Sheet2:
=DSUM(Data_All,Data_All[Reading],MATCH(A2,Data_All[Agreement]))

Evaluation fails at italicized section:
=DSUM('Sheet1'!$A2:BO$2000,'Sheet1'!$T$2:$T$2000,MATCH("110458",'Sheet1'!$AW$2:$AW$2000))

Obviously I have more columns at play here based on the evaluation returns, but it looks like it's so close to properly referencing that it's driving me nuts. Is what I'm trying to do even possible? Is it me and syntax again, or is there a better command to use?

 

[crumber_nuncher]

=SUMIF('Sheet1'!$AW$2:$AW$2000, "110458",'Sheet1'!$T$2:$T$2000)

=SUMIF(Sheet6!$A$1:$A$6,"="&A1,Sheet6!$B$1:$B$6)

Hello jarjarbingie & dreid1011,
Thanks for quick replies guys!

In a roundabout way, I stuck to my guns with table references, and incorporated both your suggestions for a clean fix.

After I reversed the range and criteria. I ended up going with:
=SUMIF(Data_All[Agreement],"="&A2,Data_All[Reading]) which resolves to
=SUMIF('Sheet1'!$AW$2:$AW$2000,"="&A2,'Sheet1'!$T$2:$T$2000)

I am going to experiment with a variation on this tomorrow =SUMIFS(), and see if I can inject more criteria in there without breaking my brain.

Thanks for pointing me in the right direction!
--Russ

 

[dreid1011]

You're welcome.