SUMIFS formula variation

[seriousdamage]

Hello,

I use a SUMIFS formula to sum various lines in a data file by choosing multiple criteria like Month, Quarter and Year
However there are situations where I don't need to select all 3 criteria, but only want the year, in this case the formula returns a value of zero.

How can I get around this?

Many Thanks
Nic

 

[steve the fish]

Oh and what is in transctions column E? A customer code or something?

 

[seriousdamage]

I have now added a blank line in the drop down list for all 3 selection lists but it still not working.
Yes in column E is a TAX code

 

[steve the fish]

Ok ive made a series of IFs. It make the assumption that if month is filled that is used first then quarter then year:

=IF('VAT summary'!D2<>"",SUMIFS(Transactions!K2:K4,Transactions!C2:C4,'VAT summary'!D2,Transactions!E2:E4,'VAT summary'!F2),IF('VAT summary'!D11<>"",SUMIFS(Transactions!K2:K4,Transactions!H2:H4,'VAT summary'!D11,Transactions!E2:E4,'VAT summary'!F2),IF('VAT summary'!E2<>"",SUMIFS(Transactions!K2:K4,Transactions!D2:D4,'VAT summary'!E2,Transactions!E2:E4,'VAT summary'!F2),"")))

 

[Aladin Akyurek]

Re: SUMIFS formula variation help

Yes, the cell is formatted into text

Assuming that =ISNUMBER(Transactions!C2) is FALSE...


=SUMIFS(Transactions!K2:K4,Transactions!C2:C4,IF('VAT summary'!D2="","?*",'VAT summary'!D2),Transactions!H2:H4,IF('VAT summary'!D11="","?*",'VAT summary'!D11),Transactions!D2:D4,'VAT summary'!E2,Transactions!E2:E4,'VAT summary'!F2)


Is this what you are after?

 

[seriousdamage]

Re: SUMIFS formula variation help

Unfortunately both formulas still not 100% right even if they are miles closer to a solution.
It looks like that the 3 selections are somehow linked, I need to be able to only chose one and leave the rest blanc if I need to.
but in your formulas, if I leave the year blanc, I never get a result.

 

[Aladin Akyurek]

Re: SUMIFS formula variation help

Unfortunately both formulas still not 100% right even if they are miles closer to a solution.
It looks like that the 3 selections are somehow linked, I need to be able to only chose one and leave the rest blanc if I need to.
but in your formulas, if I leave the year blanc, I never get a result.

Try first to test the formula with regular cells (instead of data validated cells) in order to see whether it behaves as desired.