Vlookup & If & SUM Formula

Cantrecallmyusername

Board Regular
Joined
May 24, 2021
Messages
50
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I have a sheet where I want to sum up hours for a person per month.
Tasks are entered on a row basis where the user enters the name in column C, hours per task for each month column H contains Jul, column H contains Aug, etc

On a separate sheet I want to sum all the hours for person A for each month.
I am not sure if this can be done in a vlookup, IF and SUM function as Vlookup will only return the 1st value.

I cant download the plugin to share excel file but here is an example of my dataset.
This is my raw data sheet

NameTarget DateJul-23Aug-23Sep-23Oct-23
Nov-23​
Dec-23​
Person A
12​
12​
12​
12​
12​
12​
Person A
4​
0​
1​
1​
1​
1​
Person A
14​
14​
14​
14​
14​
14​
Person A
26​
31​
0​
0​
0​
0​
Person B
7​
7​
0​
0​
0​
0​
Person B
40​
40​
0​
0​
0​
0​

My desired outcome is to have on a separate sheet the person name listed in column A and the dates across row 1 and a formula to pull back the sum values from the dataset above

Name Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23
Person A 56 57 27 27 27 27
Person B 47 47 0 0 0 0

I hope this makes sense and thank you in advance for your help.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I have attempted to use the below formula and I believe I am going in the right direction but not quiet there yet.

Excel Formula:
=SUM(INDEX(WIP!$A$2:$K$6,1,MATCH(Sheet2!A2,WIP!A2:K6,1)))
 
Upvote 0
Perhaps
Excel Formula:
=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7;F1:K1=F1))
 
Upvote 0
Perhaps
Excel Formula:
=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7;F1:K1=F1))
Hi thanks for the reply.
I am getting an error with that formula

1692265929223.png
 
Upvote 0
Try this:

Book1
ABCDEFGH
1NameTarget DateJul-23Aug-23Sep-23Oct-23Nov-23Dec-23
2Person A121212121212
3Person A401111
4Person A141414141414
5Person A26310000
6Person B770000
7Person B40400000
8
9
10Name Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
11Person A565727272727
12Person B47470000
Sheet1
Cell Formulas
RangeFormula
C11:H12C11=SUMIF($A$2:$A$7,$A11,INDEX($C$2:$H$7,0,MATCH(C$10,$C$1:$H$1,0)))
 
Upvote 0
Solution
Try this:

Book1
ABCDEFGH
1NameTarget DateJul-23Aug-23Sep-23Oct-23Nov-23Dec-23
2Person A121212121212
3Person A401111
4Person A141414141414
5Person A26310000
6Person B770000
7Person B40400000
8
9
10Name Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
11Person A565727272727
12Person B47470000
Sheet1
Cell Formulas
RangeFormula
C11:H12C11=SUMIF($A$2:$A$7,$A11,INDEX($C$2:$H$7,0,MATCH(C$10,$C$1:$H$1,0)))
Thats great - this worked for me as I required, thank you.
 
Upvote 0
That is because I omitted to replace the semi colon with a comma - Sorry
Try
Excel Formula:
=SUMPRODUCT(--(A2:A7="Person A")*FILTER(F2:K7,F1:K1=F1))
No problem at all ;-)
The initally issue is resolved however the formula is not returning the answer for me.

1692284728898.png

1692284763885.png
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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