SUMPRODUCT Formula for Multiple Conditions/Colums

[ezlea1]

Hello. I've searched all over and tried everything I've found to come up with a formula for the following:

I have a master sheet with 200 or so User IDs, months of the year, and production data (the chart below is just a small sample). I want to be able to enter a User ID in cell B10 and a month in cell B11 and have the result in B12. There are some blank/empty cells throughout the sheet. I've tried so many different things that I've gotten totally confused. Any help would be greatly appreciated. Thanks.

This is not working: SUMPRODUCT(--(A1:G7=B10),--(A1:G7=B11),--(A1:G7="Errors"),(A1:G7))
[TABLE="width: 333"]
<tbody>[TR]
[TD="class: xl68, width: 65, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 62, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl67, width: 62, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl67, width: 63, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl67, width: 63, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]Mar
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]User ID
[/TD]
[TD="class: xl68, bgcolor: transparent"]Lines
[/TD]
[TD="class: xl68, bgcolor: transparent"]Errors
[/TD]
[TD="class: xl68, bgcolor: transparent"]Lines
[/TD]
[TD="class: xl68, bgcolor: transparent"]Errors
[/TD]
[TD="class: xl68, bgcolor: transparent"]Lines
[/TD]
[TD="class: xl68, bgcolor: transparent"]Errors
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]TJC0546
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]107
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]178
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]146
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]DJA0047
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]416
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]324
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]361
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]DLB0009
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"][/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]210
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]TJC2168
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]250
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]200
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]239
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]TJC9735
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]301
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]280
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]294
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, colspan: 2"]Lines
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]User ID:
[/TD]
[TD="bgcolor: transparent"]TJC2168
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Month:
[/TD]
[TD="bgcolor: transparent"]Feb
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lines:
[/TD]
[TD="bgcolor: transparent"]200
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

=SUMPRODUCT(B3:G7*(A3:A7=B10)*(B1:G1=B11)*(B2:G2="Errors"))

 

[ezlea1]

Try:

=SUMPRODUCT(B3:G7*(A3:A7=B10)*(B1:G1=B11)*(B2:G2="Errors"))

Thanks Eric!

 

[Eric W]

Happy to help!