This is a thinker: Sumproduct with Index/Match

[Wannabepro]

Hi Everyone,

So this is my equation

{=IFERROR(SUMPRODUCT(--('1'!$A$5:$A$33='Monthly'!A2), INDEX('1'!$N$5:$N$33,,MATCH('Monthly'!A2, '1'!$A$5:$A$33, 0))),"")}
{=IFERROR(SUMPRODUCT(--('1'!$A$5:$A$33='Monthly'!A3), INDEX('1'!$N$5:$N$33,,MATCH('Monthly'!A3, '1'!$A$5:$A$33, 0))),"")}

It is taking a group of people who work and ads their pay. The problem I am coming across is that it will only add the first person's pay and only if I put them in the first line A5. If I put that same person in subsequent cells it ads them just fine but if I start them in say A6 or further it will not add them at all. In addition this should be adding different names as it goes down the list but it will only take the one name if any. That is what the 'Monthly'!A2, A3 is for. To select a different name.

Any help would be appreciated!

 

[Aladin Akyurek]

What is the problem you want to solve? Care to post 5 rows from column A of 1 and 5 rows from column N of 1?

 

[FDibbins]

INDEX/MATCH will always only find the 1st match it gets to, then stop looking. If you want to add stuff, use SUMIFS instead

 

[Wannabepro]

using SUMIF or SUMPRODUCT will yield the same result. Either way I only get the the sum of the name if the name is on A5 first if the name I am looking for starts on any other line like A6 or A10 it will not start adding them up. This baffles me. And then the second part of the issue is that moving down the line it ignores the next name you are looking for. I would be willing to use SUMIF definitely but I don't get a different outcome.

 

[Wannabepro]

How do I post clips from my spreadsheet? I would post examples if I could figure that out. This is my first time posting on a forum.

 

[Aladin Akyurek]

How do I post clips from my spreadsheet? I would post examples if I could figure that out. This is my first time posting on a forum.

Select 5 rows from A and paste here.

Again select from column N and paste here under the first paste.

 

[Wannabepro]

Here is sample column "A" on tab '1'
1 DATE: 01 November 2017
2
3 NAME
4
5 Chrystal
6 Barbara
7 Carrie
8 Claudia
9 Chrystal
10 Carrie

Here is sample column "A" on tab 'Monthly'

1 NAME
2 Alex
3 Barbara
4 Carrie
5 Chrystal



Here is sample column "N" on tab '1'
1
2
3 $ to Check
4
5 100.00
6 45.00
7 10.00
8 0.00
9 50.00
10 10.00

So for example: if I am looking for Chrystal it will give me the $150 but if I am looking for Carrie it will not give me the $20 unless Carrie's name is in the 5 position? But then it will not give me Chrystal's information even on a new equation looking for just her name because she now longer occupies the first line. Any ideas on how to fix this?

 

[Aladin Akyurek]

Sheet: 1

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

N​

[/td][/tr][tr][td]

1​

[/td][td]DATE: 01 November 2017[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]NAME[/td][td][/td][td]$ to Check[/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Chrystal[/td][td][/td][td]

100​

[/td][/tr]
[tr][td]

6​

[/td][td]Barbara[/td][td][/td][td]

45​

[/td][/tr]
[tr][td]

7​

[/td][td]Carrie[/td][td][/td][td]

10​

[/td][/tr]
[tr][td]

8​

[/td][td]Claudia[/td][td][/td][td]

0​

[/td][/tr]
[tr][td]

9​

[/td][td]Chrystal[/td][td][/td][td]

50​

[/td][/tr]
[tr][td]

10​

[/td][td]Carrie[/td][td][/td][td]

10​

[/td][/tr]
[tr][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Sheet: Monthly

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]NAME[/td][td]Total $[/td][/tr]
[tr][td]

2​

[/td][td]Alex[/td][td]

0​

[/td][/tr]
[tr][td]

3​

[/td][td]Barbara[/td][td]

45​

[/td][/tr]
[tr][td]

4​

[/td][td]Carrie[/td][td]

20​

[/td][/tr]
[tr][td]

5​

[/td][td]Chrystal[/td][td]

150​

[/td][/tr]
[/table]

In B2 of Monthly just enter and copy down:

=SUMIFS('1'!N:N,'1'!A:A,$A2)

 

[Wannabepro]

Thank you I will try it.

 

[Wannabepro]

Thank you soooooo much!!! The equation worked... I think I was doing the sumif incorrectly. =SUMIFS('1'!N:N,'1'!A:A,$A2) and your visual brought it all together! I love you all