Way over my skies with indexing, aggregates and multiple criteria!

Status
Not open for further replies.

Execu2v

New Member
Joined
Dec 27, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
PROBLEM: I am attempting to make customer lists and can't figure out how to include multiple customers that meet the required 3-part criteria. The goal: "simple" lists of cust names ranked by revenue variance. I have the Rank, and $Varience calculations done, but stumped from there. -- A newbie with indexing and aggregate functions, my formula attempts thus far only show the first listing that meets the $Variance critera (not always the right CustType). Is there a multi-critera formula (for field 3 below) that will 1) key to the variance amount, 2) filter/require the right customer type, AND 3) show multiple CustNames that share same segment AND variance amount?

The Goal: Lists by ranked sales variance for customers by Type IDs.
CustType | Rank | $Variance | CustName

(Simplified) The source data fields:
A:A Customer Name
B:B Customer Type (four segments, A, B, C and D)
C:C Sales Variance.

Really struggling here. Any/all genius help appreciated!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
It would help if you would post a small sample and the expected result.
You may want to look at the FILTER function. Something like below.

Book2
ABCDEFG
1Cust NameTypeVarSearchTypeVar <=
2Name1A10NameA15
3Name2B20Name1
4Name3C30Name9
5Name4D40Name13
6Name5A20
7Name6B20
8Name7C30
9Name8D40
10Name9A10
11Name10B20
12Name11C30
13Name12D40
14Name13A10
15Name14B20
Sheet1
Cell Formulas
RangeFormula
E3:E5E3=FILTER($A$2:$A$15,($B$2:$B$15=$F$2)*($C$2:$C$15<=$G$2),"No Match")
Dynamic array formulas.
 
Upvote 0
AhoyNC, thank you for taking time to review and for the recommendation. The data posting protocol was as daunting as the original problem, however your Filter suggestion helped direct me to a workable end. I used =UNIQUE(FILTER($H$2:$H$3042,($DV$5=$AF$2:$AF$3042) * (DU8=$AE$2:$AE$3042))). It allowed me to use multiple criteria while also seeing multiple entities that shared criterial. ... The new SPILL error was another hurdle to over come? Again, I appreciate your want to help others. Impressive.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top