Sumifs with OR

[tannaroo]

I am trying to use the following SUMIFS with OR but am getting an error message - can anyone help?

I would like to sum a column with figures if either the date is 31.Dec or todays date but its not workinng. It doesn't like the apostrophes around the "=" with the added curly bracket.

=SUMIFS(G$4:G2174, B$4:B2174, {"=" & DATE(YEAR(B2174),12,31), "=" & TODAY()}, V$4:V2174, "=" & U2174, T$4:T2174,"="&"YE")

 

[RoryA]

Just do two SUMIFS and add them:

=SUMIFS(G$4:G2174, B$4:B2174, "=" & DATE(YEAR(B2174),12,31), V$4:V2174, "=" & U2174, T$4:T2174,"=YE")+SUMIFS(G$4:G2174, B$4:B2174, "=" & TODAY(), V$4:V2174, "=" & U2174, T$4:T2174,"=YE")

 

[tannaroo]

Thanks Rory- the problem is that section of the formula is just a small part of the overall formula, so its quite complicated already. See the full formula.
I just want to add an OR function for the date to be either 31 Dec or todays date - is there a simpler way to execute?

((SUMIFS(G$4:G2174,B$4:B2174,"="&DATE(YEAR(B2174),12,31),V$4:V2174,"="&U2174,T$4:T2174,"="&"YE") - SUMIFS(AD$4:AD2174,B$4:B2174,"="&DATE(YEAR(B2174),12,31),V$4:V2174,"="&U2174,T$4:T2174,"="&"YE")) * (SUMIFS(H$4:H2174,B$4:B2174,"="&DATE(YEAR(B2174),12,31),V$4:V2174,"="&U2174,T$4:T2174,"="&"YE"))) - ((SUMIFS(G$4:G2174,B$4:B2174,"="&DATE(YEAR(B2174) - 1,12,31),V$4:V2174,"="&U2174,T$4:T2174,"="&"YE") - SUMIFS(AD$4:AD2174,B$4:B2174,"="&DATE(YEAR(B2174) - 1,12,31),V$4:V2174,"="&U2174,T$4:T2174,"="&"YE")) * (SUMIFS(H$4:H2174,B$4:B2174,"="&DATE(YEAR(B2174)- 1,12,31),V$4:V2174,"="&U2174,T$4:T2174,"="&"YE")))

 

[RoryA]

You could use SUMPRODUCT:

=SUMPRODUCT(G$4:G2174*((B$4:B2174=DATE(YEAR(B2174),12,31))+(B$4:B2174=TODAY()))*(V$4:V2174=U2174)*(T$4:T2174="YE"))

 

[tannaroo]

Thank you. I am getting really close but still getting a DIV/0 on this part of the formula, even though there are no zeroes that its feeding off. I will see if I can solve it from here.

=(SUMPRODUCT(H$4:H2174*((B$4:B2174=DATE(YEAR(B2174),12,31))+(B$4:B2174=TODAY()))*(V$4:V2174=U2174)*(T$4:T2174="YE")))

 

[tannaroo]

I think I found the issue, there are some blanks in my rows hence why its gives a DIV/O error, If I modify the formula to avoid any blanks it works!

Is there anyway to slightly modify the sumproduct formula to ignore the blanks/remove the DIV/0 error?

 

[RoryA]

I don't follow. There is no division in that formula so I don't see how you could get DIV/0 from it unless the cells it is looking at have a DIV/0 error in them.

 

[tannaroo]

Thank you - that was it!