How to count a column if it matches data from another column in seperate rows.

[Troisi]

I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.

Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.

Thanks a lot

[Bedford_Excel]

The best way to do this is to create a count in a third column. Use a function sililar to: IF( A>B,1,0) If the sent date is more then the due date return a 1 if not then 0. Now you can sum the new column to find out how many times you had that event.

[DonkeyOte]

A few points re: your file

I would change the formula in D such that:

D2: =IF(N(C2)>B2,C2-B2,"")
copy down

I would change formula in E such that:

E2: =IF(N(A2),MONTH(A2),"")
copy down

To get the count you can use a SUMPRODUCT... eg:

H2: =SUMPRODUCT(--(E2:E500=G2),--(ISNUMBER(D2:D500)))
where G2 holds month number

I would however suggest you look into using Pivot Tables, see:
http://peltiertech.com/Excel/Pivots/pivotstart.htm

They are designed for this very type of task.

[ChemistB]

You can use a SUMPRODUCT for each month For Jan in your example

=SUMPRODUCT(--($E$2:$E$35=1),--($D$2:$D$35>0),--(ISNUMBER($D$2:$D$35)))

Does that work for you? As an aside, you wouldn't need the Month Column. Just change the formula to

=SUMPRODUCT(--(MONTH($A$2:$A$35)=1),--($D$2:$D$35>0),--(ISNUMBER($D$2:$D$35)))

[DonkeyOte]

ChemistB

I would make two Points re: your formulae:

1 - Given D is only numeric if > 0 you need not test both for ISNUMBER and > 0 as illustrated an ISNUMBER test shall suffice.

2 - Re: using MONTH Column A -- this is risky if it's likely the range in use will contain blanks... given a blank equates to 0 and in date terms 0 is 00 Jan 1900 (ignoring 1904 date system) ... blanks when coerced with MONTH will return a value of 1 ... for this reason you should add an additional ISNUMBER test on the range.

[Troisi]

Ive gotten past the 1 problem for the month. That wasnt really a biggy. Im concerned with getting a count

[ChemistB]

Good Point D.O Thanks

[Troisi]

Yeah i use pivot tables. in this case though this is what i need. the data needs to be used in another table. lot of shitty stuff. but thanks everyone. will try everything out now

[Troisi]

What i need exactly is to get a count for D:D for all instances when E:E is = to g2 ( the month number) but i cant figure out the formula to do that correct.y :S

[DonkeyOte]

The formula provided does that.

Post the file you are using presently.

[Troisi]

Its the same one i uploaded. I changed the formulas just as you said but it gives me 4.. for all months actually.

[DonkeyOte]

Really ??
I guess I'll just have to do the work and upload the file.

[Troisi]

hmmm weird... but thanks a lot I honestly just copy and pasted. Would you mind explaining the formula a bit donkeyote maybe i can understand it better. not to bother you anytime soon

[DonkeyOte]

=SUMPRODUCT(--(E2:E500=G2),--(ISNUMBER(D2:D500)))

For each cell in E2:E500... = G2 ... will return Boolean TRUE/FALSE ... the double unary -- will coerce the Boolean to it's integer equivalent (1/0 in native XL respectively)

For each cell in D2:D500 ... ISNUMBER ... will equally return Boolean TRUE/FALSE which is also coerced.

So you end up with 2 arrays (E & D) of 499 values consisting of either 1 or 0 and you sum the Products of those arrays... say:

E2 = G2, D2 number
E3 <> G2, D3 number

For row 2: PRODUCT(1,1) => 1 [1 x 1]
For row 3: PRODUCT(0,1) => 0 [0 x 1]

Sum of those Products = 1 [1+0]

[Troisi]

thanks a lot perfect explanation