Using Titles of rows to sum date in columns.....

K

kgray28

New Member
Joined
May 23, 2019
Messages
8
Hi

I have a spreadsheet, with a lot more data than below spanning quite a few years. I want to add a summary page to show the totals by individual by year. As it's a moving piece i didn't want to put a YTD total on and use a sumif, i was wondering if there is a way of looking across a row and picking up all the 2019's by Boris and summing it?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2019[/TD]
[TD]2019[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Boris[/TD]
[TD]400[/TD]
[TD]400[/TD]
[TD]6500[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Carly[/TD]
[TD]500[/TD]
[TD]6500[/TD]
[TD]3000[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD]640[/TD]
[TD]502[/TD]
[TD]5031[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD]514[/TD]
[TD]545[/TD]
[TD]6565[/TD]
[TD]555[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
How about

Book1
ABCDEFGHI
1201920192018201820192018
2Boris4004006500600Boris8007100
3Carly50065003000800Megan11425191
4Megan6405025031160
5Harry5145456565555
List
Cell Formulas
RangeFormula
H2=SUMPRODUCT(($A$2:$A$5=$G2)*($B$1:$E$1=H$1),$B$2:$E$5)
 
Upvote 0
Not entirely clear but is this, copied across and down any help?
If not, more details/explanation/examples please.

Excel Workbook
ABCDEFGHIJ
1201920192018201820192018
2Boris4004006500600Boris8007100
3Carly50065003000800Carly70003800
4Megan6405025031160Megan11425191
5Harry5145456565555Harry10597120
Totals
 
Upvote 0
I see you have a successful outcome but I had missed the point in your thread title "Using Titles of rows" so for completeness this would be the adaptation of my earlier suggestion.

Excel Workbook
ABCDEFGHI
1201920192018201820192018
2Boris4004006500600Boris8007100
3Carly50065003000800Megan11425191
4Megan6405025031160Harry10597120
5Harry5145456565555Carly70003800
Totals
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Number of unique daily clock-ins
Replies
3
Views
553
marshak
M
N
  • Question Question
Easiest way to get total.
Replies
3
Views
482
awoohaw
awoohaw
T
  • Question Question
filter dynamic data without filter function
Replies
1
Views
2K
Eric W
Eric W
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
432
johnnyL
J
S
  • Solved
using advanced filter with 2 criteria one having a list & the other having text or no text
Replies
7
Views
825
RoryA
RoryA

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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