Help with Sumproduct formula with multiple criteria

Factual Questions
1

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

2

Reported for forum change.

3

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.

4

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.

5

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.