Count values based on multiple checks

[Reinder]

In addition to my previous post, I have another requirement to add to the formula.

I would like to sum all values based on the "month" as well as the "Part" column (see attached).

[BadlySpelledBuoy]

In D2 and copied down:

Please Login or Register  to view this content.

In D8 and copied down:

Please Login or Register  to view this content.

BSB

[AliGW]

Your 10 July column header is not 2018 - it's 2002 - so the correct answer is 13. That aside, you are missing a criterion:

=SUMPRODUCT((MONTH(H$1:K$1)=C2)*(YEAR(H$1:K$1)=2018)*(H$2:K$8)*(Table1[Part]=$B$2))

[Srikanth H N]

For your sumproduct formula add this as well.... *(Table1[Part]=$B$2). I tried it and giving correct results

[congnt92]

Try this
D2=SUMPRODUCT(($G$2:$G$8=$B$2)*($H$2:$K$8)*(MONTH($H$1:$K$1)=C2))
D8=SUMPRODUCT(($G$2:$G$8=$B$8)*($H$2:$K$8)*(MONTH($H$1:$K$1)=C8))

Adding criteria for year if you want.

[AliGW]

Please note that most of the solutions offered have the YEAR criterion missing.

[BadlySpelledBuoy]

Please note that most of the solutions offered have the YEAR criterion missing.

Morning Ali. Merely me being pedantic (again), but the provided solutions you refer to will replicate the OP's "expected results" so perhaps the year criteria was part of the issue...

I'll get back in my box

BSB

[AliGW]

The year criterion was not the reason for the very high value returned - that was due to missing the fourth criterion. The year is, I am convinced, a mistake in the header, which the OP assumes is also 2018 (look at the colour coding we were given for guidance).

You can get out of your box again now.

[Reinder]

Very perceptive of you, didn't notice Thanks for the solution, that was indeed simpler then I anticipated. Is my assumption correct in the fact that any additional validation can just be added in the formula?

[AliGW]

Within reason, yes.

Are you confirming that I was right about the year? Just want to know so that I can gloat a bit ...

[Reinder]

haha, not sure if it was 2002, but i was definitely not 2018. And regarding the other solutions; yes, yours was the best (and easiest ). So gloat away!

[AliGW]

Tada! Back of the net ...

Thanks for marking the thread as solved.