Values based on multiple criteria including grouped criteria

Your lookup criteria does not match anything that I see. However I did provide formulas for lookups and changed your criteria.

Also when providing a workbook please have a small example instead of the entire workbook because it is too large.

Thanks

Formula:

Please Login or Register  to view this content.

Hi

I looked at your file and formula and it seems you swapped Gear Company name to Gearbox
Also your formula does not include my 3rd criteria which are listed in CRITERIA-1 sheet

I need to report values from column U (report sheet) with criteria as follows:
* periods - mentioned in cells C5,C5 then C22, C23 then C39, C40 on MAIN SHEET
* department (Gear Company, Mollarts and DSG) mentioned in line 23 and column D, E, F on MAIN SHEET
* material type - on CRITERIA-1 SHEET

thanks

You have C5 twice. I am opting out because of users like you not spending the time to pay attention to details.

There has to be consistency on values when looking up matches.

Good luck

Hi
thanks

Agreed ... there is typo and is supposed to be C5 , C6

Hi
My post was pretty clear ... formula to return values from REPORT sheet based on criteria:
* periods - mentioned in cells C5,C6 then C22, C23 then C39, C40 on MAIN SHEET
* department (Gear Company, Mollarts and DSG) mentioned in line 23 and column D, E, F on MAIN SHEET
* material type - on CRITERIA-1 SHEET

thanks

Can you explain

D15=46,995.81 ?

E15=0 ?

F15=0 ?

Hi

after sum all values from REPORT sheet (cell U) for period 21-26/02/20 bearing in mind that all criteria are met for Gear Compnay (one of the criteria) I will get 46,995.81 which I would need to report in cell D15
and subsequently for Mollarts (one of the criteria) i would get result as "zero" and also for DSG (one of the criteria) I would get "zero"

Thnaks

The result you want to get doesn't have to consider dates.

Look at the file

D15=SUMIFS(REPORT!$U$2:$U$1000,REPORT!$K$2:$K$1000,MAIN!D$6,REPORT!$L$2:$L$1000,'CRITERIA-1'!$A$2&"*")

copy across

Hi
it needs to consider dates as well as this report is downloaded only between 21-26/02 but raw data file is from 2015
Maybe it was my mistake to share only file where 21-26 dates are included

however formula I'm looking for needs to also included period criteria

thanks

Hi

I applied below formula however it returns "zero" value for Gear Company
(I created table for raw data - REPORT sheet)

=SUMIFS(Table1[PODetNetTotal], Table1[PODepartment], MAIN!$D$6, Table1[PartGroupCode], 'CRITERIA-1'!$A$1:$A$4, Table1[POOrderDate**], ">="&MAIN!$C$5, Table1[POOrderDate**], "<="&MAIN!$C$6)

thanks

MAIN

D15=SUMPRODUCT((REPORT!$F$2:$F$1000>=MAIN!$C$5)*(REPORT!$F$2:$F$1000<=MAIN!$C$6)*(REPORT!$K$2:$K$1000=MAIN!D$6)*(ISNUMBER(MATCH(REPORT!$L$2:$L$1000,'CRITERIA-1'!$A$1:$A$4,0)))*(REPORT!$U$2:$U$1000))

Copy across

D32=SUMPRODUCT((REPORT!$F$2:$F$1000>=MAIN!$C$22)*(REPORT!$F$2:$F$1000<=MAIN!$C$23)*(REPORT!$K$2:$K$1000=MAIN!D$6)*(ISNUMBER(MATCH(REPORT!$L$2:$L$1000,'CRITERIA-1'!$A$1:$A$4,0)))*(REPORT!$U$2:$U$1000))

copy across



D49=SUMPRODUCT((REPORT!$F$2:$F$1000>=MAIN!$C$39)*(REPORT!$F$2:$F$1000<=MAIN!$C$40)*(REPORT!$K$2:$K$1000=MAIN!D$6)*(ISNUMBER(MATCH(REPORT!$L$2:$L$1000,'CRITERIA-1'!$A$1:$A$4,0)))*(REPORT!$U$2:$U$1000))

Copy acroos

Hi

thanks
I was thinking about sumproduct and match as well

i applied your solution however it returns "zero" value
trying to find our why

thanks

file attached

Attached file

Hi

returns "zero"

Attachment 668408

Have you seen my file?

the results for the dates indicated are those

Hi
Yes I have seen your file and when all filters removed it returns "zero" value :/

file attached

I don't understand what it means

Explains better

Hi

huge apologies ...

I have just noticed dates in the file I was looking at are changed (silly me)
my mistake

formula is working

thank you