SUMIF?

JTL9161

Active Member
Joined
Aug 29, 2012
Messages
344
Trying to put together a formula that I think would be a SUMIF but I may be barking up the wrong tree.

I am using letters to simplify.

I have about 250 names in column A on spreadsheet A.
I have 3 other spreadsheet tabs B, C, D
I put a value and date on spreadsheet A column B & C next to the corresponding name in column A.
In column D I put which spreadsheet this person belongs to B, C or D.

So it should look something like this
Nameamountdatespreadsheet
John10011/18/2020B
Bob10011/17/2020C
Steve10011/16/2020D


What I am trying to do is get this info on spreadsheet A to appear (in the case's above) John's info on spreadsheet B in the same format. Bob's on C and Steve's on C.
Basically from the A spreadsheet I want all the B spreadsheet people show just on B the C's on C and the D's on D

Hope I explained this for everyone to understand.
Appreciate any help
Thank you
James
 

Some videos you may like

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

RustEE2020

New Member
Joined
Feb 21, 2020
Messages
30
Office Version
  1. 2016
I do not think you can do this without using VBA when you say formula it makes it sound like you are unwilling to use VBA is this true?
Are we just copying the data or do we need relative references in case you update the info on sheet A?
 

ekrause

New Member
Joined
Aug 7, 2019
Messages
49
The easiest way to do this would be to sort on Column D and then copy and paste them to the various spreadsheets - you can either do this manually or using VBA as mentioned by @RustEE2020

There's a way to do it formula driven but it assumes you have unique identifiers in the Names Column or that you can create a helper column in your source sheet where you concatenate Name&Date&Spreadsheet into a column. Helper Column is better because you can have the same person/company/entity in column B with different dates but they need to have a unique Name & Date for this to work appropriately.

Source Spreadsheet with Helper Column

1605741460723.png


Destination Spreadsheet with Helper Column (note name of spreadsheet in cell A1)

1605741580817.png


Formula (starting in Cell F2)

=INDEX(OFFSET(Sheet7!$B$2:$B$13,0,MATCH(F$1,Sheet7!$B$1:$F$1,0)-1),MATCH(0,INDEX((COUNTIF(F$1:F1,OFFSET(Sheet7!$B$2:$B$13,0,MATCH(F$1,Sheet7!$B$1:$F$1,0)-1))+(Sheet7!$E$2:$E$13<>B!$A$1))*1,0,0),0))

Then INDEX Match off of the helper column.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,687
Office Version
  1. 365
Platform
  1. Windows
Another possible option with formulas.
If you have Excel 365 with the FILTER function take a look at the formula in E2 other wise try the formula in A2 and copy down and across as needed.
The formula need to be put in each spreadsheet.

Spreadsheet named B
Book1
ABCDEFG
1NameamountdateNameamountdate
2John10011/18/2020John10011/18/2020
B
Cell Formulas
RangeFormula
A2:C2A2=IFERROR(INDEX(A!$A$2:$C$4,AGGREGATE(15,6,(ROW(A!$A$2:$A$4)-ROW(A!$A$2)+1)/(A!$D$2:$D$4="B"),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")
E2E2=FILTER(A!$A$2:$C$4,A!$D$2:$D$4="B","")


Spreadsheet A
Book1
ABCD
1Nameamountdatespreadsheet
2John10011/18/2020B
3Bob10011/17/2020C
4Steve10011/16/2020D
A
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,687
Office Version
  1. 365
Platform
  1. Windows
JTL9161 - It would help if you would update your profile so we know which version of Excel you are using.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,012
Messages
5,575,545
Members
412,677
Latest member
Davejf81
Top