• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumifs for multiple criteria

Loz Jordan

New Member
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.
 

Attachments

Chihiro

Excel Ninja
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.
Code:
=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.
 

Luiz Carlos

New Member
Hello!
Try the formula below:

Code:
=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)))
 
Top