items changed/revised per month

[Olimpiainfante]

Hi there,
I have 2 columns,
Column I lists Months
Column K lists whether a drawing was revised, superseded or deleted

I need to know how many times during a month was the drawing either revised, supersed or deleted.

I am using =SUMPRODUCT((I2:I1300="Jan")*(K2:K1000="Revised")) but it gives me an error code of "N/A" and if I change it around it comes back $Name?

I might be using the wrong formula.....HELP PLEASE

 

[RalphA]

Have you tried using SUM, instead of SUMPRODUCT, as in:
=SUM((I2:I1300="Jan")*(K2:K1000="Revised"))
It worked for me,

 

[barry houdini]

Your ranges have to be the same size in SUMPRODUCT, try changing K1000 to K1300

=SUMPRODUCT((I2:I1300="Jan")*(K2:K1300="Revised"))

...also, does column I contain text, e.g. "jan", "feb" or does it contain dates formatted as "mmm"?

 

[Olimpiainfante]

I tried with the =sum but it did not work for me....also my column does contain text such as "Jan" "Feb" not numbers.....could that be the problem?

 

[Olimpiainfante]

I figured it out......I used SUMPRODUCT with the formula you gave me and for some reason.....IT WORKED!!!! YES!!!!

Thank you very very very much!!!!

 

[RalphA]

I would like to apologize for stating that my "-SUM()" function worked for me!

It did, apparently, but it was for a particular set of data. The result gave the correct answer, but the procedure was wrong, and only appears to work when the data is presented in a certain way.

Barry Houdini gave the right formula to use, =SUMPRODUCT(), using the same number of rows in each part.

Olimpiainfante said that Barry's formula worked,

I used SUMPRODUCT with the formula you gave me and for some reason.....IT WORKED!!!!

Here's the reason. Analyzing Barry's formula:
=SUMPRODUCT((I2:I1300="Jan")*(K2:K1300="Revised"))
It says that if "Jan" is contained in a given row of the range I2:I1300, to make (I2:I1300="Jan") equal to "TRUE". And, if the same row in K2:K1300 contains "Revised", then make (K2:K1300-"Revised") also equal to "TRUE". Now, the product (*) makes each TRUE become the number 1, which makes the product of the two quantities 1. Now, the function SUMPRODUCT has first done the product, obtaining a 1, then it does a SUM, obtaining the number of times both "Jan" and Revised" occurred on the same row.

Barry, have I explained this right?