# SumifS formula question

#### Deified

##### New Member
=SUMIFS(Transactions!\$AI:\$AI,Transactions!\$A:\$A,\$E\$3,Transactions!\$D:\$D,\$B7)

This is the formula I am using and I want to have the formula work like this =SUMIFS(Transactions!\$AI:\$AI,Month(Transactions!\$A:\$A),\$E\$3,Transactions!\$D:\$D,\$B7)

Where the criteria looks at the month so see if it matches the month in cell E3. Is there a way that I can make this work with another formula?

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Tetra201

##### MrExcel MVP
Try this (note: not tested):

=SUMPRODUCT(Transactions!\$AI\$1:\$AI\$10000,--(MONTH(Transactions!\$A\$1:\$A\$10000)=\$E\$3),--(Transactions!\$D\$1:\$D\$10000=\$B7))

#### Deified

##### New Member
comes back #value!

I should clarify. in the formula =SUMPRODUCT(Transactions!\$AI\$1:\$AI\$10000,--(MONTH(Transactions!\$A\$1:\$A\$10000)=\$E\$3),--(Transactions!\$D\$1:\$D\$10000=\$B7))

Transactions!\$AI\$1:\$AI\$10000 = the value I want pull / summarize. There are multiple entries for each name

MONTH(Transactions!\$A\$1:\$A\$10000)=\$E\$3 = the month that I want it to summarize. (there are weekly values added and I want a month at a time displayed)

Transactions!\$D\$1:\$D\$10000=\$B7)) = the names of the employees

Last edited:

#### Tetra201

##### MrExcel MVP
comes back #value!
Works OK on my test sheet.

What exactly do you have in \$E\$3 and in Transactions!\$A:\$A? -- A few examples would be helpful.

#### live_excel

##### New Member
Use this

=SUMIFS(Transactions!\$AI:\$AI,Transactions!\$A:\$A,"="&\$E\$3,Transactions!\$D:\$D,"="&\$B7)

This will work perfectly. For more help you can visit ExcelChat for free, instant match, personalized help from paid certified experts in Excel.

#### Deified

##### New Member
Works OK on my test sheet.

What exactly do you have in \$E\$3 and in Transactions!\$A:\$A? -- A few examples would be helpful.

 REPORTING_DAY NAME TOTAL 11/27/16 XX 10 11/27/16 CC 4174 11/27/16 VV 6541 11/27/16 BB 1234

<tbody>
</tbody>

This is what the transactions tab looks like (with real names though). The date column is column A and I want it to sumif based on the month (there is going to be a year long list of dates on this and I want to pull a month at a time) Column B above is column D in the formula, and Column C above is column AI in the formula.

Cell E3 is the month value (11) in this case. Cell B7 is the name (xx) in this case. So I want to summarize the totals column based on the name and the month. but I don't want to create a new column to single out the month to make that happen.

#### Deified

##### New Member
This would work, but the problem still remains that I can have it sum against the month

#### Tetra201

##### MrExcel MVP
 REPORTING_DAY NAME TOTAL 11/27/16 XX 10 11/27/16 CC 4174 11/27/16 VV 6541 11/27/16 BB 1234

<tbody>
</tbody>

This is what the transactions tab looks like...
I see. Try adjusting the ranges in the formula to exclude column headers (otherwise MONTH(...) would return an error), for example:

=SUMPRODUCT(Transactions!\$AI\$2:\$AI\$10000,--(MONTH(Transactions!\$A\$2:\$A\$10000)=\$E\$3),--(Transactions!\$D\$2:\$D\$10000=\$B7))

#### Deified

##### New Member
I see. Try adjusting the ranges in the formula to exclude column headers (otherwise MONTH(...) would return an error), for example:

=SUMPRODUCT(Transactions!\$AI\$2:\$AI\$10000,--(MONTH(Transactions!\$A\$2:\$A\$10000)=\$E\$3),--(Transactions!\$D\$2:\$D\$10000=\$B7))

That was it!!! Thank you very much!!

You are welcome.

Replies
9
Views
195
Replies
0
Views
172
Replies
1
Views
183
Replies
1
Views
347
Replies
2
Views
350

1,190,767
Messages
5,982,822
Members
439,799
Latest member
matts12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back