# SUMIF?

#### JTL9161

##### Active Member
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 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
 Name amount date spreadsheet John 100 11/18/2020 B Bob 100 11/17/2020 C Steve 100 11/16/2020 D

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

### 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
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
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.

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

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
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.

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","")

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

#### JTL9161

##### Active Member
Thank you for your input! I will see if this works for me.

#### AhoyNC

##### Well-known Member
JTL9161 - It would help if you would update your profile so we know which version of Excel you are using.

Replies
3
Views
97
Replies
1
Views
64
Replies
1
Views
112
Replies
16
Views
130
Replies
5
Views
85