Use a formula in the SUMIFS criteria range

[mcleester]

Is there a way to use a formula in the sumifs function criteria range?

For example, say there column A has dates and column B has sales figures. If I wanted to find all the sales in December 2011, I would normally create two extra columns C =year(A1) and D =month(A1).

Then my formula would be sumifs(B1:B10,C1:C10,2011,D1:D10,12). It would obviously be ideal to not have to create extra columns and be able to do:

sumifs(B1:B10,year(A1:A10),2011,month(A1:A10),12)

That doesn't work and I can't seem to use array formulas (the ones with the {}) to do it within the function either. Anyone know a way?

 

[njimack]

Welcome to the board...
=SUMPRODUCT(--(YEAR(A1:A10)=2011),--(MONTH(A1:A10)=12),(B1:B10))

 

[Aladin Akyurek]

Is there a way to use a formula in the sumifs function criteria range?

For example, say there column A has dates and column B has sales figures. If I wanted to find all the sales in December 2011, I would normally create two extra columns C =year(A1) and D =month(A1).

Then my formula would be sumifs(B1:B10,C1:C10,2011,D1:D10,12). It would obviously be ideal to not have to create extra columns and be able to do:

sumifs(B1:B10,year(A1:A10),2011,month(A1:A10),12)

That doesn't work and I can't seem to use array formulas (the ones with the {}) to do it within the function either. Anyone know a way?

Try...

E1: 1-Dec-11

F1:

=SUMIFS($B$1:$B$10,$A$1:$A$10,">="&$E1,$A$1:$A$10,"<"&EDATE($E1,1))

 

[MrVillareal]

Is there a way to use a formula in the sumifs function criteria range?

For example, say there column A has dates and column B has sales figures. If I wanted to find all the sales in December 2011, I would normally create two extra columns C =year(A1) and D =month(A1).

Then my formula would be sumifs(B1:B10,C1:C10,2011,D1:D10,12). It would obviously be ideal to not have to create extra columns and be able to do:

sumifs(B1:B10,year(A1:A10),2011,month(A1:A10),12)

That doesn't work and I can't seem to use array formulas (the ones with the {}) to do it within the function either. Anyone know a way?

if you have 2011data with 2011dates and 2011amounts try,

=SUMIFS(2011AMOUNT,2011DATES,">=1-DEC-2011",2011DATES,"<=31-DEC-2011)

or you can put those criteria in a cell and reference them try,
A1 = 1-DEC-2011
A2 = 31-DEC-2011

=SUMIFS(2011AMOUNTS,2011DATES,">="&A1,2011DATES,"<="&A2)

hope this help.

 

[Mohammad Basem]

You may also use

=SUMPRODUCT(Sales,(MONTH(Date)=12)+0,(YEAR(Date)=2011)+0)

Date = dates in column A
Sales = Amounts in column B

 

[mcleester]

Welcome to the board...
=SUMPRODUCT(--(YEAR(A1:A10)=2011),--(MONTH(A1:A10)=12),(B1:B10))

That works great! can you explain to me why you need the double negative signs? The other replies are really cool too...this board is crazy. Thanks for all the help.

 

[Aladin Akyurek]

That works great! can you explain to me why you need the double negative signs? The other replies are really cool too...this board is crazy. Thanks for all the help.

SumIfs faster...

 

[mcleester]

SumIfs faster...

I will have to take the time to more fully understand that formula then too. I think if I used yours, I would just create a "report table" that had start date and end dates for "reporting periods" and reference those dates in your formula. Thanks so much!

 

[rcorum]

Hi!!! I have a question. Is it possible for me to have sumifs function and use criteria range that consists of formulas.

Thank you!

 

[Aladin Akyurek]

Hi!!! I have a question. Is it possible for me to have sumifs function and use criteria range that consists of formulas.

Thank you!

Yes. It depends of course of the type of the formula you need as criteria range. Care to elaborate?