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]

Re: SUMIFS formula variation help

Paste the formula here.

 

[seriousdamage]

Re: SUMIFS formula variation help

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

 

[steve the fish]

Re: SUMIFS formula variation help

Ok whats in the various criteria cells? The ones in VAT summary. Paste those here.

 

[seriousdamage]

Re: SUMIFS formula variation help

Not sure how to do it in a way that looks good :

Transactions!K2:K4 = invoice net amounts in euros
Transactions!C2:C4 = Months (October 2018)
Transactions!H2:H4 = Quarters (Q12018)
Transactions!D2:D4 = Years (2018)

The criteria for the 3 conditions are picked in the tab 'VAT summary' from a drop down list and are besically Month, quarter and year

Does this help?

Thanks
Nic

 

[steve the fish]

Re: SUMIFS formula variation help

So how can that one formula do quarters and months? Its not possible far as I can see.

 

[seriousdamage]

Re: SUMIFS formula variation help

What formula can I use to select and sum data from a data file by making one and/or more selections?

 

[Aladin Akyurek]

Re: SUMIFS formula variation help

Is C2:C4 text, not number?

 

[seriousdamage]

Re: SUMIFS formula variation help

Is C2:C4 text, not number?

Yes, the cell is formatted into text

 

[steve the fish]

Re: SUMIFS formula variation help

If the cells in Vat summary are dropdowns do you have a blank for when you dont need the month or quarter just the year?