=SUMPRODUCT help with 3 criteria's

I currently use this formula for 2 variables

=SUMPRODUCT((Database!$D$2:$D$200=$B6)*(Database!$E$2:$E$200=C$4))

Basically looks like this, (sorry I don't know how to format)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

As you can see, sheet 2 adds up the number by matching the correct text, Building A and Red and so on.

I want to add another criteria by adding a month of when the door was painted if possible? so

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Right now, mine only checks by matching the building name and door colour, I was wondering how do I add a month criteria into the equation? is the best way by adding the month name to match or simply by date?

I hope this makes sense. I simply just want to use another criteria to check month aswell.

could anyone enlighten me too please,

regards

In Sheet2 you can put dates in the cells E4, F4 etc. which are the first of the month, e.g. 1/01/2017, 1/02/2017, and apply a custom format to those cells of "mmmm" to display "January", "February" and so on, OR you could just enter the month name as text, whichever you find most convenient. However, the formula would have to be different in the two cases. First case (with dates):

=SUMPRODUCT((Database!$D$2:$D$200=$B6)*(Database!$E$2:$E$200=C$4)*(MONTH(Database!$F$2:$F$200)=MONTH(E$4)))

Second case (text months):

=SUMPRODUCT((Database!$D$2:$D$200=$B6)*(Database!$E$2:$E$200=C$4)*(TEXT(Database!$F$2:$F$200,"mmmm")=E$4))

If you have blank dates in the range F2:F200 it would be better to use the second approach, to avoid errors.

Hope this helps.

Pete.

P.S. In future it would be better to attach a sample Excel workbook, so we don't have to guess what ranges you are using.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Pete

=SUMPRODUCT((Database!$D$2:$D$200=$B6)*(Database!$E$2:$E$200=C$4)*(TEXT(Database!$c$2:$c$200,"mmmm")=d$3))

is what I think you need if your dates are in column C

Originally Posted by davsth

=SUMPRODUCT((Database!$D$2:$D$200=$B6)*(Database!$E$2:$E$200=C$4)*(TEXT(Database!$c$2:$c$200,"mmmm")=d$3))

is what I think you need if your dates are in column C

I'm still getting the same number as without by adding the new criteria.

if I understand, to break down the formula

*(TEXT(Database!$F$2:$F$200,"mmmm")=d$3))

TEXT = do I leave this as just TEXT?

Database!$F$2:$F$200 = the month displayed on that sheet

mmmm = formatted cell by month name only

d$3 = name of the month of the 2nd sheet

is that correct?

in the end result, I'm simply just trying to add up if A + B + C = 1

if A + B only, that is not 1. hence why C is the 3rd factor to include, which is the date/month

Why not attach a spreadsheet as Pete suggests above, following his instructions. Your dates could be text and visa versa. It makes an answer much easier to give.

Why can't you just attach a sample Excel workbook, as I described in Post #2, and then we can see what cells and ranges you are using.

Pete

You can put this formula in C6 of Sheet2:

=SUMPRODUCT(('1'!$C$2:$C$200=$B6)*('1'!$D$2:$D$200=C$4)*(TEXT('1'!$A$2:$A$200,"mmmm")=$D$3))

Copy it across into D6 to get this formula:

=SUMPRODUCT(('1'!$C$2:$C$200=$B6)*('1'!$D$2:$D$200=D$4)*(TEXT('1'!$A$2:$A$200,"mmmm")=$D$3))

then copy both formulae down. Note that you do not need to commit these formulae with Ctrl-Shift-Enter.

Then you might like to change one or more of the dates in column A of Sheet1 to see the effect.

Hope this helps.

Pete

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

The first condition is checking for the building in B6 (i.e. A), but when the formula is copied down it then checks for the building in B7 (i.e. B). In the second condition it is still looking for a Red door, and there is only 1 red door which is missing in building A. (I'm not sure how a door can be red AND be missing !).

If you want to show 4 missing doors and 4 doors of the wrong colour, then the building term and the colour term should be omitted, so you would have this in N5:

=SUMPRODUCT(('1'!$E$2:$E$202=M$5)*(TEXT('1'!$A$2:$A$202,"mmmm")=$D$3))

Hope this helps.

Pete

not if you are still insisting that the building is A and the door is red!