Countifs with named ranges

[sirN]

Sorry if this has been asked 1000 times.

I have some named ranges and am trying to do a "Countifs" based off the named ranges. But Excel is giving me obvious incorrect data (my fault, not Excel's). I can't really post a pic of the issue, hopefully it's an obvious error on my part.

For instance

SpecialPart is a named range consisting of part numbers that I want to count seperately from all others.

PartNumber is a named range of all parts shipped for a period.

I used countifs becuase I am also checking by month. The month part works fine because I use it in other countifs. It's just this one that isn't working for me.

My formula looks like this;

=Countifs(Pmonth,Month(A3),PartNumber,SpecialPart)

As mentioned, the month part of the formula works in other calculations, but the Part/SpecialPart calculation keeps returning a "1" even though there are multiple instances of a match for each month. A manual count of January showed about 100 lines that should have been counted.

Hope I explained this well.

 

[Andrew Poulsom]

If SpecialPart contains more than one cell (an array) you need:

=SUMPRODUCT(COUNTIFS(Pmonth,MONTH(A3),PartNumber,SpecialPart))

 

[sirN]

If SpecialPart contains more than one cell (an array) you need:

=SUMPRODUCT(COUNTIFS(Pmonth,MONTH(A3),PartNumber,SpecialPart))

Thank you so much!