SUMIFS criteria having problems with considering all values matching between two columns

[raksone]

Hello great community!

I have been quite stuck with the following formula, which at this point I need your great help.

What I want to do is the following: I want to sum the values of column 'MO Dump 2'!S:S if the customer date is before 'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15 if the customer status is equal to 'MO Dump 2'!L:L,"="&"Current" and only for the matching values between these two columns 'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG:AG, but the latter seems not to be working as expected.

Here is the formula:

=SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG:AG)

Am I doing it right?

Thanks.

 

[DanteAmor]

Hi and welcome to MrExcel!

Check if the following array formula is what you need.

Dante Amor
	A
1	22
Sheet1

Cell Formulas
Range	Formula
A1	A1	=SUM(IF(('MO Dump 2'!H2:H100<='UV Calculations Dump'!AE15)* ('MO Dump 2'!L2:L100="Current")* (ISNUMBER(MATCH('MO Dump 2'!T2:T100,'UV Calculations Dump'!AG:AG,0))), 'MO Dump 2'!S2:S100))
Press CTRL+SHIFT+ENTER to enter array formulas.

It is recommended in an array formula is that you delimit the range of the cells, in this example I am delimiting from 2 to 100. You can consider a margin, if you have 500 rows, then change the 100 to 1000.

 

[raksone]

Hello DanteAmor,

Thank you for replying. Unfortunately, the formula doesn't work as intended.

I worked it out by using this formula, however it is too enormous and repeated due to the third criteria of each, and if I want to do it with multiple criteria that are very similar, I will have to use a very large formula. Is it possible to shorten it?

=SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG4)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG5)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG6)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG7)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG8)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG9)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG10)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG11)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG12)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG13)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG14)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG15)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG16)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG17)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG18)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG19)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG20)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG21)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG22)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG23)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG24)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG25)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG26)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG27)+SUMIFS('MO Dump 2'!S:S,'MO Dump 2'!H:H,"<="&'UV Calculations Dump'!AE15,'MO Dump 2'!L:L,"="&"Current",'MO Dump 2'!T:T,"="&'UV Calculations Dump'!AG28)

 

[DanteAmor]

To test my formula, you could upload your file to the cloud. It is important that you manually put the result you want in your file.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.