# Help with Sumproduct formula with multiple criteria

**URL:** <https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533>\
**Category:** Factual Questions\
**Created:** [February 13, 2015, 3:00am UTC](https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533 "2015-02-13T03:00:40Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Airangel](https://avatars.discourse-cdn.com/v4/letter/a/c68b51/32.png) [@Airangel](https://boards.straightdope.com/u/Airangel)\
**Post date:** [February 13, 2015, 3:00am UTC](https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533/1 "2015-02-13T03:00:40Z")

</div>

Hello, I am new to this so hopefully I get this sent right…

I am trying to calculate all 4 x 8 Brick types sold for the month of November (i.e. 4 x 8 BRICK and 4 x 8 20BRICK). In my example data below there were 10, 15 and 1 for a total of 26 4 x 8 Bricks sold in November. I get #VALUE! with this formula - Help! The access query pulls in spaces into the excel file so those are accounted for.

=SUMPRODUCT(1\*(MONTH(A2:A6)=11),B2:B6,C2:C6,"=4 x 8 BRICK ","COMP 4 x 8 BRICK ")

dtdate dtqty dtbkdesc  
10-Nov-14 5 4 x 12 LOGO BRICK  
10-Nov-14 10 4 x 8 BRICK  
10-Nov-14 1 8 x 8 PREM LOGO GRANITE BRICK  
11-Nov-14 15 4 x 8 BRICK  
11-Nov-14 2 8 x 8 BRICK  
12-Nov-14 1 4 x 8 20BRICK

---

<div class="post-metadata">

**Author:** ![running\_coach](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/running_coach/32/15836_2.png) [@running\_coach](https://boards.straightdope.com/u/running_coach)\
**Post date:** [February 13, 2015, 3:08am UTC](https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533/2 "2015-02-13T03:08:52Z")

</div>

Reported for forum change.

---

<div class="post-metadata">

**Author:** ![engineer\_comp\_geek](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/engineer_comp_geek/32/504_2.png) [@engineer\_comp\_geek](https://boards.straightdope.com/u/engineer_comp_geek)\
**Post date:** [February 13, 2015, 3:20am UTC](https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533/3 "2015-02-13T03:20:41Z")

</div>

**Moderator Action**

Welcome to the SDMB, **Airangel**.

The About This Message Board forum is for topics about the message board, such as problems posting, questions about rules here, etc. Since this is a factual question, it belongs in the General Questions forum.

Moving thread from About This Message Board to General Questions.

---

<div class="post-metadata">

**Author:** ![mcgato](https://avatars.discourse-cdn.com/v4/letter/m/ac8455/32.png) [@mcgato](https://boards.straightdope.com/u/mcgato)\
**Post date:** [February 13, 2015, 2:33pm UTC](https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533/4 "2015-02-13T14:33:44Z")

</div>

You can try entering:  
=SUM((MONTH(A2:A7)=11)_B2:B7_(LEFT(C2:C7,5)=“4 x 8”))

then hit ctrl - shift - enter, instead of enter. I’m not really sure how that ctrl-shift-enter stuff works, but it seems to give the right answer.

---

<div class="post-metadata">

**Author:** ![regginbrow](https://avatars.discourse-cdn.com/v4/letter/r/ba9def/32.png) [@regginbrow](https://boards.straightdope.com/u/regginbrow)\
**Post date:** [February 13, 2015, 3:10pm UTC](https://boards.straightdope.com/t/help-with-sumproduct-formula-with-multiple-criteria/712533/5 "2015-02-13T15:10:23Z")

</div>

The problem is with the (MONTH(A2:A6)=11) part of the formula. The MONTH function cannot apply to a range, unless entered in an array form, as **mcgato** has demonstrated. You’re also better off using a SUM formula anyway, since you are not actually multiplying multiple columns together other than in order to determine criteria.