SumifS formula question

[Deified]

=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?

 

[Tetra201]

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]

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

 

[Tetra201]

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]

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]

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]

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

 

[Tetra201]

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]

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!!

 

[Tetra201]

You are welcome.