excel formula help - sumifs with multiple condition

Question

How can I apply the sumifs function for this scenario without changing the format of the raw data.

In the raw data, I have the sales for each month, I want to sumifs the sales by quarterly and first half years

Q1 sales = sumifs(Sheet1! B:B, Sheet1!A:A, "=Feb or Mar or Apr")

Q2 sales = sumifs(Sheet1! B:B, Sheet1!A:A, "=May or  Jun or Jul")

Q3 sales = sumifs(Sheet1! B:B, Sheet1!A:A, "=Aug or Sep or Oct")

Q4 sales = sumifs(Sheet1! B:B, Sheet1!A:A, "=Nov or Dec or Jan")

H1 sales = sumifs(Sheet1! B:B, Sheet1!A:A, "=Feb or Mar or Apr or May or Jun or Jul")

The reason that I have to use sumifs, it is because I have add up more conditions into the formula later on...

but I can't figure out the how to sum it by quarterly is my first step.

thank you.

Answer 1

I will suggest you use a helper column which will give you month number as per your financial year. In your case it will be Feb To Jan.  So it will give 1 for Feb and 12 for Jan.

Use this formula in cell C2 and copy it down.

=IF(A2="Jan",12,MONTH(1&A2)-1)

Now use this formula for Quarter or Half yearly reports.

=SUMIFS(B:B,C:C,">=1",C:C,"<=3")

In this way you will find formula is more flexible and you may calculate for Qtr, Half year or yearly results. Even in stead of formula you may refer cells and it will make your formula dynamic.

Answer 2

Refer below image:

Formula in cell D2 - 1st Qtr (Feb to Apr):

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))>=2)*(MONTH(1&LEFT(A2:A100,3))<=4)*(B2:B100))

Formula in cell D3 - 2nd Qtr:

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))>=5)*(MONTH(1&LEFT(A2:A100,3))<=7)*(B2:B100))

Formula in cell D4- 3rd Qtr:

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))>=8)*(MONTH(1&LEFT(A2:A100,3))<=10)*(B2:B100))

Formulas in cell D5 & D6 (2 options) - 4th Qtr (Nov, Dec & Jan):

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))>=11)*(B2:B100)+(MONTH(1&LEFT(A2:A100,3))=1)*(B2:B100))

=SUMPRODUCT(((MONTH(1&LEFT(A2:A100,3))>=11)+(MONTH(1&LEFT(A2:A100,3))=1))*(B2:B100))

Formula in cell C2 - 1st Half - Feb to July:

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))>=2)*(MONTH(1&LEFT(A2:A100,3))<=7)*(B2:B100))

Formula in cell C3 - 2nd Half:

=SUMPRODUCT(((MONTH(1&LEFT(A2:A100,3))>=8)+(MONTH(1&LEFT(A2:A100,3))=1))*(B2:B100))

These formulas will be flexible and work without a helper column.

You may also shorten the formulas and use as below

Ex. for 1st Qtr (Feb to Apr):

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))={2,3,4})*(B2:B100))

For 2nd Qtr:

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))={5,6,7})*(B2:B100))

For 3rd Qtr:

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))={8,9,10})*(B2:B100))

and for 4th Qtr (Nov, Dec & Jan):

=SUMPRODUCT((MONTH(1&LEFT(A2:A100,3))={1,11,12})*(B2:B100))

and similarly for 6-mths.

Regards,

Amit Tandon

www.globaliconnect.com

Answer 3

You didn't wrap it in a SUM(), though. It only works with the multiple arguments when you wrap it in a SUM() function. 

=sum(sumifs('POS RAW'!H:H, 'POS RAW'!A:A, 'Dept'!A1, 'POS RAW'!F:F, {"FEB", "MAR", "APR}))

Answer 4

Hi

THank you for your quick reply....

I tried your formula and combined with my other condition, but it doesn't work

=sumifs('POS RAW'!H:H, 'POS RAW'!A:A, 'Dept'!A1, 'POS RAW'!F:F, {"FEB", "MAR", "APR}))

Answer 5

Hello,

you can use this syntax and wrap the Sumifs into a Sum

=SUM(SUMIFS(B:B,A:A,{"Feb","Mar","Apr"}))