Index/Match Question

[eprice]

I am trying to pick up multiple accounts with the same value using the Index/ Match Formula and I have two questions on the formula. I have another column that further breaks down the accounts and is there a way in the formula to take the other accounts into account. The other question is i am trying to sum the match results found and it is only picking up the first value of the accounts, which is $1013.72.Here is the formula and any help adjusting the formula for the two questions would be appreciated.

=(INDEX('Data Sheet'!$D$2:$D$997,SUM(MATCH(732000,'Data Sheet'!$B$2:$B$997,0),1)))

1070110 732000 EQUIPMENT REPAIRS 0
1070120 732000 EQUIPMENT REPAIRS 1013.72
1070140 732000 EQUIPMENT REPAIRS 0
1070150 732000 EQUIPMENT REPAIRS 0
1070180 732000 EQUIPMENT REPAIRS 0
1070420 732000 EQUIPMENT REPAIRS 264.88
1070430 732000 EQUIPMENT REPAIRS 1478
1070460 732000 EQUIPMENT REPAIRS 342.5
1070120 732000 EQUIPMENT REPAIRS 1050
1070460 732000 EQUIPMENT REPAIRS 135.5

[NBVC]

For the 2nd question, you need Sumif()

e.g.

=SUMIF('Data Sheet'!$B$2:$B$997,732000,'Data Sheet'!$D$2:$D$997)

Not sure what you are saying for the 1st question...can you elaborate a bit more?

[eprice]

1070110 732000 EQUIPMENT REPAIRS 0
1070120 732000 EQUIPMENT REPAIRS 1013.72
1070140 732000 EQUIPMENT REPAIRS 0
1070150 732000 EQUIPMENT REPAIRS 0
1070180 732000 EQUIPMENT REPAIRS 0
1070420 732000 EQUIPMENT REPAIRS 264.88
1070430 732000 EQUIPMENT REPAIRS 1478
1070460 732000 EQUIPMENT REPAIRS 342.5
1070120 732000 EQUIPMENT REPAIRS 1050
1070460 732000 EQUIPMENT REPAIRS 135.5

Account 732000 is Broken Down into different sub accounts to which they are assigned 1070110, 1070120, ect..So my first question was basically can you use the match function or multiple match functions if I want to take out one of the sub accounts, like this...

=(INDEX('Data Sheet'!$D$2:$D$997,SUMIf(MATCH(732000(subtract sub accounts here like - 1070460),'Data Sheet'!$B$2:$B$997,0),1)))

[NBVC]

Perhaps...

=SUMPRODUCT(--('Data Sheet'!$A$2:$A$997=1070460),--('Data Sheet'!$B$2:$B$997=732000),'Data Sheet'!$D$2:$D$997)

[eprice]

Sorry let me explain further the sub accounts will appear in almost every account such as the list below and thanks for the sumif formula it did the trick for the second question.So I need a formula that can subtract out a range of sub accounts that might not be desired.Sumif provided in second post only provides the undesired sub account so i guess i can do the first post suggestion minus the second and that would work, but any help in shortening would be appreciated.


=(SUMIF('Data Sheet'!$B$2:$B$997,732000,'Data Sheet'!$D$2:$D$997))-(SUMPRODUCT(--('Data Sheet'!$A$2:$A$997=1070460),--('Data Sheet'!$B$2:$B$997=732000),'Data Sheet'!$D$2:$D$997))

1070110 732000 EQUIPMENT REPAIRS 0
1070120 732000 EQUIPMENT REPAIRS 1013.72
1070140 732000 EQUIPMENT REPAIRS 0
1070150 732000 EQUIPMENT REPAIRS 0
1070180 732000 EQUIPMENT REPAIRS 0
1070420 732000 EQUIPMENT REPAIRS 264.88
1070430 732000 EQUIPMENT REPAIRS 1478
1070460 732000 EQUIPMENT REPAIRS 342.5

1070120 623500 MATERIAL HANDLING SUPPLY 200
1070150 623500 MATERIAL HANDLING SUPPLY 200
1070410 623500 MATERIAL HANDLING SUPPLY 119
1070420 623500 MATERIAL HANDLING SUPPLY 200
1070440 623500 MATERIAL HANDLING SUPPLY 0

[NBVC]

Maybe just change = to <> in the Sumproduct() formula?

=SUMPRODUCT(--('Data Sheet'!$A$2:$A$997<>1070460),--('Data Sheet'!$B$2:$B$997=732000),'Data Sheet'!$D$2:$D$997)

[eprice]

Thank you very much that solved the issue. Now I have one last question, which is how to take into account a range of things i want to take out. I used the folowing formula, but wont take into account a range.


SUMPRODUCT(--('Data Sheet'!$A$2:$A$997<>211000:211999),--('Data Sheet'!$B$2:$B$997=732000),'Data Sheet'!$D$2:$D$997)

[NBVC]

Try:

=SUMPRODUCT((('Data Sheet'!$A$2:$A$997<211000)+('Data Sheet'!$A$2:$A$997>211999)),--('Data Sheet'!$B$2:$B$997=732000),'Data Sheet'!$D$2:$D$997)

[eprice]

Thank you very much for all the help, you must be an excel master.