# Sum Product By Month & By Specific Text

1. ## Sum Product By Month & By Specific Text

I currently have this formula: =SUMPRODUCT((\$E\$2:E82)*(MONTH(\$B\$2:B82)=1))

I need to add the additional criteria to my formula: IF D2:D70 = "TBY"

I can't figure out how to get that additional criteria to sum product only if Column D equals TBY.

Thank you!

2. ## Re: Sum Product By Month & By Specific Text

Hi Chrystal,

Try

=SUMPRODUCT((\$E\$2:E82)*(MONTH(\$B\$2:B82)=1)*(\$D\$2:D82="TBY"))

The range in rows of all arrays must be the same.

3. ## Re: Sum Product By Month & By Specific Text

I get a #Value error when I enter that formula
.

I have three options only for the D column of TBY, CGY, MTL but some rows aren't completed yet so cells are Blank in all three columns.

4. ## Re: Sum Product By Month & By Specific Text

Are all the cells in column E numbers?
Are all the cells in column B dates?
Are all the cells in column D Text?

5. ## Re: Sum Product By Month & By Specific Text

Yes

Column E = Numbers Only (100.001)
Column B = Dates Only (Only 2016 dates)
Column D = Text Only (TBY, CGY, MTL - as a drop down menu option between the three)

6. ## Re: Sum Product By Month & By Specific Text

I tested your formula (with blank cells) and it worked fine so suggests erroneous data type somewhere.

7. ## Re: Sum Product By Month & By Specific Text

B D E
DATE DEST WEIGHT
1-Feb CGY 100.694
4-Feb MTL 96.162
11-Feb MTL 95.990
12-Feb CGY 91.761
12-Feb TBY 96.192
13-Feb CGY 100.095
14-Feb CGY 96.272
14-Feb TBY 94.806
16-Feb CGY 96.258
17-Feb TBY 96.142
BLANK BLANK BLANK
BLANK BLANK BLANK...

Below in Rows 100-120 I have a chart in Columns A-E

In my equation I have the range specified to end at Row 82 (all arrays are 2:82 so they all equal)

Thank you!

8. ## Re: Sum Product By Month & By Specific Text

I had no problem with the above data: we are not going to resolve this unless you post your actual Excel file.

9. ## Re: Sum Product By Month & By Specific Text

and after adding the file, as requested in #7.

I would use a pivot table to solve this problem.

There are currently 1 users browsing this thread. (0 members and 1 guests)