Sumifs, index, match, lookup need to sum data based on both row condition and column condition

S

Sailrmac

New Member
Joined
Feb 1, 2013
Messages
2
I need to sum data based on both the row matching a condition (e.g. =rent) and the column meeting a condition (e.g. =1.10.BL.000.16.3A). For instance in the example below I need a formula that causes the cell to sum to 133.

[TABLE="width: 787"]
<TBODY>[TR]
[TD][/TD]
[TD]1.10.BL.000.16.15</SPAN>
[/TD]
[TD]1.10.BL.000.16.1Q</SPAN>
[/TD]
[TD]1.10.BL.000.16.3A</SPAN>
[/TD]
[/TR]
[TR]
[TD]Rent</SPAN>
[/TD]
[TD]54[/TD]
[TD][/TD]
[TD]133
[/TD]
[/TR]
[TR]
[TD]Utilities</SPAN>
[/TD]
[TD]263[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building Repairs</SPAN>
[/TD]
[TD]217[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</TBODY>[/TABLE]



[TABLE="width: 627"]
<TBODY>[TR]
[TD][/TD]
[TD]1.10.BL.000.16.15</SPAN>
[/TD]
[TD]1.10.BL.000.16.1Q</SPAN>
[/TD]
[TD]1.10.BL.000.16.3A</SPAN>
[/TD]
[/TR]
[TR]
[TD]Building Repairs</SPAN>
[/TD]
[TD]217</SPAN>
[/TD]
[TD][/TD]
[TD]2</SPAN>
[/TD]
[/TR]
[TR]
[TD]Equip Rental R&M</SPAN>
[/TD]
[TD]76</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rent</SPAN>
[/TD]
[TD]54</SPAN>
[/TD]
[TD][/TD]
[TD]103</SPAN>
[/TD]
[/TR]
[TR]
[TD]Rent</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]30</SPAN>
[/TD]
[/TR]
[TR]
[TD]Utilities</SPAN>
[/TD]
[TD]263</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I can't get sumif to work because sometimes the data I want to sum is in column B (1.10.BL.000.16.15) and sometimes it is in column D (1.10.BL.000.16.3A). I don't want to just hand address those columns but instead have it lookup or match to find the correct column to sum. Sumifs doesn't seem to work because the criteria are not just down two different rows, rather one is in a row (=rent) and the other a column (=1.10.BL.000.16.3A).
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Hi,
Given in A1:
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-page-orientation:landscape;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {vertical-align:middle; white-space:normal;} .xl64 {font-weight:700; vertical-align:middle; white-space:normal;} .xl65 {color:red; vertical-align:middle; white-space:normal;} --> </style> [TABLE="width: 532"]
<colgroup><col style="width:133pt" span="4" width="133"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl64, width: 133"]1.10.BL.000.16.15[/TD]
[TD="class: xl64, width: 133"]1.10.BL.000.16.1Q[/TD]
[TD="class: xl64, width: 133"]1.10.BL.000.16.3A[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 133"]Rent[/TD]
[TD="class: xl65, width: 133, align: right"]54[/TD]
[TD="class: xl65, width: 133, align: right"]0[/TD]
[TD="class: xl65, width: 133, align: right"]133[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 133"]Utilities[/TD]
[TD="class: xl65, width: 133, align: right"]263[/TD]
[TD="class: xl65, width: 133, align: right"]0[/TD]
[TD="class: xl65, width: 133, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 133"]Building Repairs[/TD]
[TD="class: xl65, width: 133, align: right"]217[/TD]
[TD="class: xl65, width: 133, align: right"]0[/TD]
[TD="class: xl65, width: 133, align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl64, width: 133"]1.10.BL.000.16.15[/TD]
[TD="class: xl64, width: 133"]1.10.BL.000.16.1Q[/TD]
[TD="class: xl64, width: 133"]1.10.BL.000.16.3A[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 133"]Building Repairs[/TD]
[TD="class: xl63, width: 133, align: right"]217[/TD]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl63, width: 133, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 133"]Equip Rental R&M[/TD]
[TD="class: xl63, width: 133, align: right"]76[/TD]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl63, width: 133"][/TD]
[/TR]
[TR]
[TD="class: xl63, width: 133"]Rent[/TD]
[TD="class: xl63, width: 133, align: right"]54[/TD]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl63, width: 133, align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 133"]Rent[/TD]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl63, width: 133, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 133"]Utilities[/TD]
[TD="class: xl63, width: 133, align: right"]263[/TD]
[TD="class: xl63, width: 133"][/TD]
[TD="class: xl63, width: 133"][/TD]
[/TR]
</tbody>[/TABLE]


Formula in B1 =SUMPRODUCT(($A$9:$A$13=$A2)*($B$8:$D$8=B$1)*($B$9:$D$13)) copied till D4.
Would that work?
 
Upvote 0
Unfortunately that didn't work. I just got a bunch of N/A's. Any other idea's or could I send you the file?

I really appreciate the help.

Darren
 
Upvote 0
Sailrmac said:
Unfortunately that didn't work. I just got a bunch of N/A's. Any other idea's or could I send you the file?

I really appreciate the help.

Darren
Click to expand...
Read that link (here) then try this:
Select the whole sheet.


Check for constant errors:
Edit / Go To... / Special... / Constants
un-check all options except "Errors"
Check for formula errors:
Edit / Go To... / Special... / Formulas
un-check all options except "Errors"
 
Last edited:
Upvote 0
Sailrmac said:
I need to sum data based on both the row matching a condition (e.g. =rent) and the column meeting a condition (e.g. =1.10.BL.000.16.3A). For instance in the example below I need a formula that causes the cell to sum to 133.

[TABLE="width: 787"]
<tbody>[TR]
[TD][/TD]
[TD]1.10.BL.000.16.15
[/TD]
[TD]1.10.BL.000.16.1Q
[/TD]
[TD]1.10.BL.000.16.3A
[/TD]
[/TR]
[TR]
[TD]Rent
[/TD]
[TD]54
[/TD]
[TD][/TD]
[TD]133
[/TD]
[/TR]
[TR]
[TD]Utilities
[/TD]
[TD]263
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building Repairs
[/TD]
[TD]217
[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 627"]
<tbody>[TR]
[TD][/TD]
[TD]1.10.BL.000.16.15
[/TD]
[TD]1.10.BL.000.16.1Q
[/TD]
[TD]1.10.BL.000.16.3A
[/TD]
[/TR]
[TR]
[TD]Building Repairs
[/TD]
[TD]217
[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Equip Rental R&M
[/TD]
[TD]76
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rent
[/TD]
[TD]54
[/TD]
[TD][/TD]
[TD]103
[/TD]
[/TR]
[TR]
[TD]Rent
[/TD]
[TD][/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Utilities
[/TD]
[TD]263
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I can't get sumif to work because sometimes the data I want to sum is in column B (1.10.BL.000.16.15) and sometimes it is in column D (1.10.BL.000.16.3A). I don't want to just hand address those columns but instead have it lookup or match to find the correct column to sum. Sumifs doesn't seem to work because the criteria are not just down two different rows, rather one is in a row (=rent) and the other a column (=1.10.BL.000.16.3A).
Click to expand...

A SumIf formula would suffice...

Let Sheet2, A:D, house...
[TABLE="width: 644"]
<colgroup><col style="width: 149pt; mso-width-source: userset; mso-width-alt: 7054;" width="198"> <col style="width: 160pt; mso-width-source: userset; mso-width-alt: 7566;" width="213"> <col style="width: 156pt; mso-width-source: userset; mso-width-alt: 7395;" width="208"> <col style="width: 179pt; mso-width-source: userset; mso-width-alt: 8504;" width="239"> <tbody>[TR]
[TD="class: xl63, width: 198, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 213, bgcolor: transparent"]1.10.BL.000.16.15[/TD]
[TD="class: xl64, width: 208, bgcolor: transparent"]1.10.BL.000.16.1Q[/TD]
[TD="class: xl64, width: 239, bgcolor: transparent"]1.10.BL.000.16.3A[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 198, bgcolor: transparent"]Building Repairs[/TD]
[TD="class: xl63, width: 213, bgcolor: transparent, align: right"]217[/TD]
[TD="class: xl63, width: 208, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 239, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 198, bgcolor: transparent"]Equip Rental R&M[/TD]
[TD="class: xl63, width: 213, bgcolor: transparent, align: right"]76[/TD]
[TD="class: xl63, width: 208, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 239, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl64, width: 198, bgcolor: transparent"]Rent[/TD]
[TD="class: xl63, width: 213, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl63, width: 208, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 239, bgcolor: transparent, align: right"]103[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 198, bgcolor: transparent"]Rent[/TD]
[TD="class: xl63, width: 213, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 208, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 239, bgcolor: transparent, align: right"]30[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 198, bgcolor: transparent"]Utilities[/TD]
[TD="class: xl63, width: 213, bgcolor: transparent, align: right"]264[/TD]
[TD="class: xl63, width: 208, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 239, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Let Sheet1, A:D, do the processing...

[TABLE="width: 626"]
<colgroup><col style="width: 111pt; mso-width-source: userset; mso-width-alt: 5262;" width="148"> <col style="width: 156pt; mso-width-source: userset; mso-width-alt: 7395;" width="208"> <col style="width: 149pt; mso-width-source: userset; mso-width-alt: 7082;" width="199"> <col style="width: 210pt; mso-width-source: userset; mso-width-alt: 9955;" width="280"> <tbody>[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 208, bgcolor: transparent"]1.10.BL.000.16.15[/TD]
[TD="class: xl66, width: 199, bgcolor: transparent"]1.10.BL.000.16.1Q[/TD]
[TD="class: xl66, width: 280, bgcolor: transparent"]1.10.BL.000.16.3A[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]Rent[/TD]
[TD="class: xl65, width: 208, bgcolor: transparent, align: right"]54[/TD]
[TD="class: xl65, width: 199, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 280, bgcolor: transparent, align: right"]133[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]Utilities[/TD]
[TD="class: xl65, width: 208, bgcolor: transparent, align: right"]264[/TD]
[TD="class: xl65, width: 199, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 280, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]Building Repairs[/TD]
[TD="class: xl65, width: 208, bgcolor: transparent, align: right"]217[/TD]
[TD="class: xl65, width: 199, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 280, bgcolor: transparent, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

B2, copied across and down:
Rich (BB code): 
=SUMIF(Sheet2!$A$2:$A$6,$A2,INDEX(Sheet2!$B$2:$D$6,0,
  MATCH(B$1,Sheet2!$B$1:$D$1,0)))
 
Upvote 0
Aladin Akyurek said:
A SumIf formula would suffice...

...B2, copied across and down:
Rich (BB code): 
=SUMIF(Sheet2!$A$2:$A$6,$A2,INDEX(Sheet2!$B$2:$D$6,0,
  MATCH(B$1,Sheet2!$B$1:$D$1,0)))
Click to expand...

Aladin, what would be less expensive, =SUM(IF($A$9:$A$13=$A2,IF($B$8:$D$8=B$1,$B$9:$D$13))) CSE or =SUMIF(Sheet1!$A$9:$A$13,$A2,INDEX(Sheet1!$B$9:$D$13,0, MATCH(B$1,Sheet1!$B$8:$D$8,0)))?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Sum multiple columns with Sumif Index Match
Replies
1
Views
921
SunnyAlv
SunnyAlv
G
  • Question Question
Copy Range of Cells based on Criteria, Paste it on rows below it, Do This Until All conditions are met, Then Delete All Copied Rows
Replies
0
Views
559
glaccounting
G
S
  • Solved
Sum index match multiple row multiple column - referring to an old post link inside
Replies
4
Views
1K
Alex Blakenburg
A
C
  • Question Question
Index Match that can sum multiple columns of data that match multiple column/row criteria
Replies
3
Views
1K
Marcelo Branco
M
J
  • Question Question
Pass one step of code to the next
2
Replies
16
Views
982
HaHoBe
HaHoBe

Forum statistics

Threads
1,224,548
Messages
6,179,443
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top