items changed/revised per month

Olimpiainfante

New Member
Joined
Dec 13, 2005
Messages
14
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Have you tried using SUM, instead of SUMPRODUCT, as in:
=SUM((I2:I1300="Jan")*(K2:K1000="Revised"))
It worked for me,
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Dec 13, 2005
Messages
14
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

New Member
Joined
Dec 13, 2005
Messages
14
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

Well-known Member
Joined
May 14, 2003
Messages
3,829
I would like to apologize for stating that my "-SUM()" function worked for me! :oops:

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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,650
Members
412,335
Latest member
cinciri99
Top