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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,549
Messages
5,838,044
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top