View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Claus Busch Claus Busch is offline
external usenet poster
 
Posts: 3,872
Default SUMIFS with multiple criteria provides wrong result

Hi again,

Am Tue, 24 Oct 2017 13:32:12 +0200 schrieb Claus Busch:

=SUMPRODUCT((Input!A1:A100=$I$2)*(Input!A1:A100<= EDATE($I$2,1))*(Input!G1:H100=$G$2)*(Input!B1:B100 =$G$16)*Input!F1:F100)


if you don't want to summarize F twice if also G and H match the
criteria, try:
=SUM(IF((Input!G1:G100=G2)+(Input!H1:H100=G2),(Inp ut!B1:B100=G16)*(Input!A1:A100=I2)*(Input!A1:A100 <=EDATE(I2,1))*Input!F1:F100))
and insert this formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016