# 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

• 22 KB Views: 10

#### 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.

#### Loz Jordan

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

#### 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)))``

#### Chihiro

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