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

**Troisi** · 02-06-2009, 12:58 PM

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** · 02-06-2009, 01:28 PM

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** · 02-06-2009, 02:12 PM

A few points re: your file

I would change the formula in D such that:

Please Login or Register  to view this content.

I would change formula in E such that:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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** · 02-06-2009, 02:22 PM

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

**DonkeyOte** · 02-06-2009, 02:30 PM

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** · 02-06-2009, 02:34 PM

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

**ChemistB** · 02-06-2009, 02:59 PM

Good Point D.O Thanks

**Troisi** · 02-06-2009, 09:45 PM

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** · 02-09-2009, 05:45 AM

DonkeyO i tried trying your formulas but it doesnt give me anything. i eamn the first 2 formulas work but the third one gives nothing.

**DonkeyOte** · 02-09-2009, 05:48 AM

Did you enter the Month Number you want to sum in cell G2 as advised ?

**Troisi** · 02-09-2009, 05:55 AM

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** · 02-09-2009, 05:56 AM

The formula provided does that.

Post the file you are using presently.

**Troisi** · 02-09-2009, 06:03 AM

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

**DonkeyOte** · 02-09-2009, 06:05 AM

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

**Troisi** · 02-09-2009, 06:13 AM

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** · 02-09-2009, 06:30 AM

=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** · 02-09-2009, 07:29 AM

thanks a lot perfect explanation

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions