how to match multiple criteria to get value

[akshay6s]

Hi everyone,

i have a file in which sheet "DESIRE RESULT" has matching criteria from cell A5 to C. and D4 to AH4. these both criteria have to match from sheet "DAILY PRODUCTION" from column C to G.

i want daily production to be fetched according to date, p.o. no. quality, party name for the whole month. as now there are data only for 2 dates 17 & 18 november.
but i want the formula when all month data will be available then result should be according to those criteria. and when any change in month then according to that.

i am hoping to get this as solved.

[Bo_Ry]

Please try at D5

=SUMIFS('DAILY PRODUCTION'!$E:$E,'DAILY PRODUCTION'!$G:$G,$A5,'DAILY PRODUCTION'!$F:$F,$B5,'DAILY PRODUCTION'!$D:$D,$C5,'DAILY PRODUCTION'!$C:$C,">="&D$4,'DAILY PRODUCTION'!$C:$C,"<"&D$4+1)

[Glenn Kennedy]

In your real sheet, will you REALLY be using NOW() in daily production, column C????

[akshay6s]

very very perfectly done. you are too fast and too responsive. thanks a lot from the bottom of my heart. i have to ask two things only.

first it is showing zero for the rest of the date so it is difficult to see through so much zero. is it possible that it leaves cell blanks and show data only where the data matches.

and second thing what for cell AH5 for the section of the formula 'DAILY PRODUCTION'!$C:$C,"<"&AG$4+1).
i mean to say that when next moth will come for 31 days. then this will work or not for cell AH5.

how ever i think that it will work but i want to discuss with you.

rest is fantastic. you have solved my sheet perfectly and in a very easy way. really thanks again.

[akshay6s]

for Glenn kennedy sir,

i works in google sheet. but for posting here i download them as .xls. so really sorry for that. here is the link if you like to have a look. and you always helps me thanks for that.

https://docs.google.com/spreadsheets...gid=1208098673

[Glenn Kennedy]

=IFERROR(1/(1/Bo Ry's formula),"")

will remove the forest of zeros.

[akshay6s]

=IFERROR(1/(1/Bo Ry's formula),"")

will remove the forest of zeros.

thank you very very much sir. you are great as always.

[Glenn Kennedy]

I do not use Google sheets. Is there a TODAY() function in Google sheets that works in the sam ewas as NOW()? If so, does it work for you in the same way, generating your dates. The problem is that NOW() is a comnination of date and time, whcih makes the formula a bit more complicated than needed. If TODAY() works for you in Google sheets, then you can use a slightly simpler formula and get round the AH5 problem as the dates, everywhere, will then be integers.

[akshay6s]

=iferror(1/(1/ SUMIFS('DAILY PRODUCTION'!$E:$E,'DAILY PRODUCTION'!$G:$G,$A5,'DAILY PRODUCTION'!$F:$F,$B5,'DAILY PRODUCTION'!$D:$D,$C5,'DAILY PRODUCTION'!$C:$C,">="&D$4,'DAILY PRODUCTION'!$C:$C,"<"&D$4+1),""))

this is showing error.

there is a difference in today and now function. if we enter today() in any cell it will show today's current date which is 18-11-2020. but on the next day this same cell
will show tomorrow date which will be 19-11-2020.

so for skipping this problem we use now() which contains date & time so it will never changed. and we can format these cells as date only and they will behave like normal dates.
please see daily production sheet (column c) in the given link and i have formed them as normal dates but have used now.