Vlookup, Hlookup, Index, Match, Sumifs & Countifs : How to lookup in Database

[guatelize]

Dear All
I've been trying to find a solution to create for my long list database a lookup formula with a sumif criteria in 2 columuns and a header search :
For the RawData in Col A3 has the contract number, Col B3 Delivery period, Col C3 Start Month, Col D3 the Start Quantity; Col E3 End Period & Col F3 End Quantity.
In my global overview Row A16 , I need to have formula wich calculates the totals for each month, for each contract & for each delivery time I manually insert.
Thanks for your help

 

[Hui]

C20: =SUMPRODUCT(($A$4:$A$14=$A20)*($B$4:$B$14=$B20)*(($C$4:$C$14=C$19)*($D$4:$D$14)+($E$4:$E$14=C$19)*($F$4:$F$14)))
Copy/paste to fill the rest of the Global overview area
I'd also apply a Custom Number format of: 0,000;;"";@

 

[guatelize]

Super Hui, thanks for your very fast reply. Works perfectly. But I have a small question, what if the Start Month (Col C3) is equal to End Month (Col E3), my global overview does not show the quantities in the correct row.
Thanks
Oliver

 

[Hui]

Try this revised formula in C20:
=SUMPRODUCT(($A$4:$A$14=$A20)*($B$4:$B$14=$B20)*(OFFSET($B$4:$B$14,,MATCH($B20,$C$3:$F$3,0))=C$19)*(OFFSET($B$4:$B$14,,MATCH($B20,$C$3:$F$3,0)+1)))

 

[guatelize]

Thanks, but it is not working anymore.

 

[Hui]

Whoops, My Bad

I forgot to ask you to change the following cell values

C3: START CONTRACT
E3: END CONTRACT

 

[guatelize]

Thanks, works perfectly
have a nice day

 

[Tom Mililli]

Good Day,
I have a similar question. However instead of summing by product, I would like to populate the unique fields from lookup of duplicate ones (uploaded sample)

Basically, a Index / Match / VLookup type command / function that will traverse the table rows and populate specific columns with its related unique data (i.e. AssetID, Edition, Version, etc.)
Example
Two Tabs
Source: SoftwareFeed file Column Headers --
Columns
A | B | C | D
SoftwareName|Version|Edition|AssetID

Destination: Inventory.xlsx file - Column Headers

Columns
A | B | C | D | E | F....
SoftwareName|AssetID|Edition|Qty|Price| etc.
Each destination file is per SoftwareName. From the destination file, I want to populate this table of the related columns based on Software Name.

Unfortunately, both Index / Match and VLookup will only found the first occurrence of many based on the software name.

Inventory Tab
Formula in B3:D:8
=INDEX(tblSWRFeed,MATCH($A3,tblSWRFeed[SoftwareName],0),MATCH(B$2,tblSWRFeed[#Headers],0))
Formula in K3:K8
=COUNTIFS(tblSWRFeed[SoftwareName],[@SoftwareName],tblSWRFeed[Version],[@Version],tblSWRFeed[Edition],[@Edition],tblSWRFeed[AssetID],[@AssetID])

Sample output on inventory tab (A13:D25)
Any assistance would be greatly appreciated.

 

[Faseeh]

@ Tom Mililli

Welcome to the forums, kindly start a new thread for this topic. Thanks.

 

[NARAYANK991]

Hi ,

With all your explanation , I am still not able to understand what you want ; what is the table in the range A3 through D8 ?

What is the table in the range A14 through D25 ? If your table tblSWRFeed is sorted on the columns A , B , C and D , in that order , you get this.

Can you explain what exactly you wish to do ?

Narayan