Using Filter Function how to extract and sum data when different columns have the same name?

L

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
163
Office Version
  1. 365
Platform
  1. Windows
Dear Guys,

It seams so simple that it bothers me.

Accordingly table below using Filter Function how to extract and sum data when different columns have the same name?

Choosing name from data validation in cell B3 how to filtered it with unique name, unique code and unique product and sum the total amount of units across all months?

Hope that you could help.?

Eternally grateful.?

Thank you very much.

Novo Folha de Cálculo do Microsoft Excel.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2NAME
3A
4
5
6NAMECODEPRODUCTTOTAL SUM UNITS ene.21 until abr.2021ene. 2021ene. 2021ene. 2021feb. 2021feb. 2021feb. 2021mar. 21mar. 21mar. 21abr. 2021abr. 2021abr. 2021
7NAMEBRICKCODEPRODUCTFACTUREDunitsgrossnetunitsgrossnetunitsgrossnetunitsgrossnet
8AA315 LX100ESPIRO----10504052314---
9BA315 LX200DICLO-2103--84105942
10CA315 LX300GABA-152------521
11C316 LX100ESPIRO-521--63--211
12B320 LX50AMLO----105----732
13A315 LX40AMOX-2105------632
14B320 LX10AVA------5211---
15C316 LX200DICO-25221----211
16C316 LX300GABA------2111111
17B320 LX50AMLO-252111111421
18
19
20
Folha10
Cell Formulas
RangeFormula
G8:G10G8=SORT(UNIQUE(H8:H17))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3List=$G$8:$G$10
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Not sure but:

In B7:
Excel Formula: 
=UNIQUE(FILTER(FILTER(H8:K17,{1\0\1\1},""),H8:H17=B3),0)

In E7(drag down):
Excel Formula: 
=SUMPRODUCT(IFERROR((B7&C7&D7=$H$8:$H$17&$J$8:$J$17&$K$8:$K$17)*($M$8:$X$17),""))
 
Upvote 0
JEC said:
Not sure but:

In B7:
Excel Formula: 
=UNIQUE(FILTER(FILTER(H8:K17,{1\0\1\1},""),H8:H17=B3),0)

In E7(drag down):
Excel Formula: 
=SUMPRODUCT(IFERROR((B7&C7&D7=$H$8:$H$17&$J$8:$J$17&$K$8:$K$17)*($M$8:$X$17),""))
Click to expand...
Dear @JEC unfortunately the 1st formula is not working and 2nd formula sums all columns not only the ones with "units".

Can you check again please?

@Peter_SSs can you give your feed back please?

Thanks again guys without you not be able to make it work. ????
 
Upvote 0
The first one is working here. I think it has tot do with this character: "\"
I have different regional settings

Maybe:
Excel Formula: 
=UNIQUE(FILTER(FILTER(H8:K17,{1;0;1;1},""),H8:H17=B3),0)

the second formula:
Excel Formula: 
=SUMPRODUCT(IFERROR((B7&C7&D7=$H$8:$H$17&$J$8:$J$17&$K$8:$K$17)*($M$8:$X$17)*($M$7:$X$7="units"),""))
 
Last edited:
Upvote 0
Solution
Dear @JEC thanks again.

Think just make some improvements.

The 1st formula is done accordingly my regional settings but 2nd formula it seams ok but doesn return the correct value.

Table below in order to get your feedback.

Novo Folha de Cálculo do Microsoft Excel.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2NAME
3A
4
5
6NAMECODEPRODUCTTOTAL SUM UNITS ene.21 until abr.2021LARGE UNITS ene.21 until abr.2022ene. 2021ene. 2021ene. 2021feb. 2021feb. 2021feb. 2021mar. 21mar. 21mar. 21abr. 2021abr. 2021abr. 2021
7A100ESPIRO NAMEBRICKCODEPRODUCTFACTUREDunitsgrossnetunitsgrossnetunitsgrossnetunitsgrossnet
8A200DICLO AA315 LX100ESPIRO----10504052314---
9A300GABA BA315 LX200DICLO-2103--84105942
10A40AMOX CA315 LX300GABA-152------521
11 C316 LX100ESPIRO-521--63--211
12 B320 LX50AMLO----105----732
13 A315 LX40AMOX-2105------632
14 B320 LX10AVA------5211---
15 C316 LX200DICO-25221----211
16C316 LX300GABA------2111111
17B320 LX50AMLO-252111111421
18
19
Folha10
Cell Formulas
RangeFormula
B7:D10B7=UNIQUE(FILTER(FILTER(I8:L17,I8:I17=B3),{1,0,1,1},0))
H8:H10H8=SORT(UNIQUE(I8:I17))
E7:E15E7=IFERROR(SUMPRODUCT((B7&C7&D7=$I$8:$I$17&$K$8:$K$17&$L$8:$L$17)*($N$8:$Y$17)*($N$7:$Y$7="units")),"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3List=$H$8:$H$10
 
Upvote 0
The sumproduct formula looks slightly different than mine.
You switched iferror and sumproduct
 
Upvote 0
JEC said:
The sumproduct formula looks slightly different than mine.
You switched iferror and sumproduct
Click to expand...

If you check the formula for yourself one of the range were outside:

the second formula:
Excel Formula:
=SUMPRODUCT(IFERROR((B7&C7&D7=$H$8:$H$17&$J$8:$J$17&$K$8:$K$17)*($M$8:$X$17)*($M$7:$X$7="units"),""))

Confirm please.
 
Upvote 0
It is, but not on your first sample data ;) .

Just adapt the ranges but keep the iferror after the sumproduct
 
Upvote 0
Is this what you're attempting?
Book1 (version 2).xlsb
ABCDEFGHIJKLMNOPQRSTUVWX
1
2NAME
3A
4Jan-21
5Apr-21
6NAMECODEPRODUCTUnitsJan-21Jan-21Jan-21Feb-21Feb-21Feb-21Mar-21Mar-21Mar-21Apr-21Apr-21Apr-21
7A100ESPIRO15NAMEBRICKCODEPRODUCTFACTUREDunitsgrossnetunitsgrossnetunitsgrossnetunitsgrossnet
8A200DICLO15AA315 LX100ESPIRO----10504052314---
9A300GABA6BA315 LX200DICLO-2103--84105942
10A40AMOX8CA315 LX300GABA-152------521
11C316 LX100ESPIRO-521--63--211
12B320 LX50AMLO----105----732
13A315 LX40AMOX-2105------632
14B320 LX10AVA------5211---
15C316 LX200DICO-25221----211
16C316 LX300GABA------2111111
17B320 LX50AMLO-252111111421
Sheet5
Cell Formulas
RangeFormula
B7:D10B7=UNIQUE(FILTER(FILTER(H8:K17,H8:H17=B3),{1,0,1,1}))
G8:G10G8=SORT(UNIQUE(H8:H17))
E7:E10E7=IF(D7="","",SUMPRODUCT(($H$8:$H$17=B7)*($J$8:$J$17=C7)*($K$8:$K$17=D7)*($M$7:$X$7=$E$6)*($M$6:$X$6>=$E$4)*($M$6:$X$6<=$E$5),$M$8:$X$17))
Dynamic array formulas.
 
Upvote 0
JEC said:
It is, but not on your first sample data ;) .

Just adapt the ranges but keep the iferror after the sumproduc
Click to expand...

Works Like Magic.

We made a great Team Together ;):biggrin:???.

Thank you very much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
DOUBLE FILTER function to return ONLY records with most recent date when base data is sorted continuously
2
Replies
11
Views
442
Fluff
Fluff
L
  • Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
743
Lacan
L
R
  • Solved
Using named range with FILTER function in data validation list (dynamic dropdown)
Replies
7
Views
9K
Akuini
Akuini
E
  • Solved
Picking up extra data lines using FILTER
Replies
5
Views
423
Fluff
Fluff
L
  • Question
How to extract data using extensions Names and Complete Names ???
Replies
3
Views
335
Lacan
L

Forum statistics

Threads
1,214,431
Messages
6,119,458
Members
448,899
Latest member
maplemeadows

Share this page

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