Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 13th 17, 07:52 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2017
Posts: 2
Default Sum Product Matrix

Hi all,

Trying to get a formula to return the following:

Column A: Title
Column B: Salary 1
Column C: Salary 2
Column D: Salary 3
Column E: Salary 4

Based of of this matrix if Employee n's salary falls in between one of the 4 criteria, then I would like the higher value returned

Example:

A: Manager
B: 1000
C: 5000
D: 10000
E: 15000

My lookup index: Title - Manager / Salary - 8000.

I want this to give me the value in column D. If the salary was increased to 11,000 then column E.

So far I am stuck at:

=sumproduct(--('TitleInput'=$A:$A),--($B:$B<'SalaryInput'),--($C:$C<'SalaryInput'),--($D:$D<'SalaryInput'),($E:$E))

Where I only get a returned value if the Salary input is larger than D and Smaller than E. If I make the value D or smaller I get 0.

Thanks!

  #2   Report Post  
Old March 14th 17, 01:12 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2017
Posts: 2
Default Sum Product Matrix

I have also tried:

{=INDEX($A:$E,MATCH(1,(A:A=K9)*(IF('SalaryInput'D 2,E2,IF('SalaryInput'C2,D2,IF('SalaryInput'B2,C2 ,B2)))),0),4)}

but get an #N/A error


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
where can i get a product compliance matrix template from? Nacer Excel Discussion (Misc queries) 0 August 13th 09 02:58 PM
I need a product key for my Trail product, 2007 Microsoft Office s sltchsyi Setting up and Configuration of Excel 0 May 4th 09 01:32 AM
Chose a product and update related product variables hupjack Excel Programming 0 April 9th 09 11:57 PM
Extracting data from a product matrix tarheelfan Excel Discussion (Misc queries) 0 March 25th 09 08:58 PM
Vlookup code product and to copy commentary with photo of the product in vba [email protected] Excel Programming 0 October 2nd 06 03:54 AM


All times are GMT +1. The time now is 05:55 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017