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

Lacan

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
228
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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

T
  • Question Question
How to sum with multiple criteria in 2 different columns? - census data
Replies
1
Views
158
hagia_sofia
hagia_sofia
E
  • Solved
DOUBLE FILTER function to return ONLY records with most recent date when base data is sorted continuously
2
Replies
11
Views
869
Fluff
Fluff
Lacan
  • Question Question
How to filter function and SUM based on Partial Text Match (SUMIFS with wildcards)
2
Replies
17
Views
1K
Lacan
Lacan
R
  • Solved
Using named range with FILTER function in data validation list (dynamic dropdown)
Replies
7
Views
11K
Akuini
Akuini
E
  • Solved
Picking up extra data lines using FILTER
Replies
5
Views
564
Fluff
Fluff

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

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