Formula Help Please

[garymu16]

Hi All,

I have a 2 page workbook I am currently working on. Page 1 is called Data -- Column A has account manager names, Column F has the month and Column C has the sales value. This data runs over 15000 rows currently.

On the 2nd page, Summary, I have a list of account managers from cell A2 to A12, and on row 2 (cell B2 to K2) I have the months.

What formula could I use on my Summary page to match the account manager names and the months to return the sales value?

Apologies for not being able to display a snapshot of the spreadsheet but my works computer will not permit download of the HTML maker.

Many thx in advance

Gary

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(Sheet1!$A$2:$A$1500=$A3),--(Sheet1!$F$2:$F$1500=B$2),Sheet1!$C$2:$C$1500)

in B3, drag right / down.

 

[garymu16]

Thanks Fairwinds - Worked a treat!

My next question would be - Could I modify the formula to give me the values that dont match any of the names on Column A of my summary spreadsheet?

Thx

 

[barry houdini]

For each month?

Try

=SUMPRODUCT(--ISNA(MATCH(Sheet1!$A$2:$A$1500,$A3:$A13,0)),--(Sheet1!$F$2:$F$1500=B$2),Sheet1!$C$2:$C$1500)

copied across