Sumifs for multiple criteria

[Loz Jordan]

Hi, I am new here and hoping you can help me!
I am trying to create a formula in cell D11 that will generate the total summed from column H on the actual data tab. based on the selections entered in cells C4 to C6. I have managed to create a formula that works to sum based on all location as well as individual locations for each of the months but unfortunately can't get it to work if the period is YTD and should therefore sum all data regardless of what is in column A on the 'Actual data' tab. I have attached so hopefully you will see what I mean! Grateful for any help you can offer.

 

[Chihiro]

Few issues:
1. You have Mar-19 as text, rather than date value formatted as mmm-yy.

Then I'd change formula to something like below.

=SUMIFS('ACTUAL DATA'!$H$2:$H$27,'ACTUAL DATA'!$G$2:$G$27,IF($C$6="ALL","*",$C$6),'ACTUAL DATA'!$A$2:$A$27,IF($C$4="YTD","<="&TODAY(),$C$4))

FYI: I'd recommend using dynamic named ranges to manage things.

 

[Loz Jordan]

Thank you Chihiro, unfortunately this doesn't work when you select YTD in cell C4.

 

[Luiz Carlos]

Hello!
Try the formula below:

=IF(C4="YTD",SUMIFS('ACTUAL DATA'!$H:$H,'ACTUAL DATA'!$N:$N,'P&L'!B11),IF(C6="All",SUMIFS('ACTUAL DATA'!$H:$H,'ACTUAL DATA'!$N:$N,'P&L'!B11,'ACTUAL DATA'!$A:$A,'P&L'!$C4),SUMIFS('ACTUAL DATA'!$H:$H,'ACTUAL DATA'!$N:$N,'P&L'!$B11,'ACTUAL DATA'!$A:$A,'P&L'!$C$4,'ACTUAL DATA'!G:G,'P&L'!$C$6)))

 

[Chihiro]

@Loz Jordan
Did you change the dates stored as text to actual date value?