Index and Match Question

[dturgel]

Hi,

I have three columns that I am trying to pull data from via index and match and the current formula I have is missing certain data points - any ideas?

Column 1 (account numbers) Column 2 (Revenue) Column 3 (counts accounts left 5) Column 4 (sums revenue by account)
10001-11 $10,000 1 (Trying to get $60,000 here)
10001-21 $20,000 0 (Trying to get $0 here)
10001-31 $30,000 0 (Trying to get $0 here)

We have accounts that are linked by the first part of the value in column 1. Column 2 shows the value for each part of the account. Column 3 refers back to column 1 and is just set up to count the "10001" one time so we avoid counting the same account more than once (that formula is working fine: =IF(LEFT(A2,5)=LEFT(A3,5),0,1).

It's the next column that I can't figure out. So far what I've got is the following: =IF(C2=0,0,SUM(B2:INDEX(B:B,MATCH(TRUE,INDEX(C:C=1,0),0))))

I tried inserting a Left function but that didn't work. I'm lost - any suggestions?

Daniel

 

[Marcelo Branco]

Hi Daniel

Maybe...

D2
=IF(C2=0,0,SUMPRODUCT(--(LEFT($A$2:$A$100,5)=LEFT(A2,5)),$B$2:$B$100))
copy down

Hope this helps

M.

 

[C Moore]

Would SUMIFS not work? =sumifs(B:B,C2,1,A:A,left(A2,5)&"*")

 

[123rickfear]

if you change your column C to give you the 10001 like this =IF(LEFT(A2,5)=LEFT(A1,5),0,LEFT(A2,5))

then this will work.
=IF(C2=0,0,SUMIF(A:A,C2&"*",B:B))

 

[Marcelo Branco]

Would SUMIFS not work? =sumifs(B:B,C2,1,A:A,left(A2,5)&"*")

Yes, it works

M.