A formula pls.

  • Thread starter Kirandeep Singh
  • Start date
K

Kirandeep Singh

Hi friends,

I have two coloumns 'Reason for leaving' and respective 'date of leaving'

I want to find out which people left in which month. Like there is a reason
called abandoned, so I would like to know how many abandoned in the month of
January and so on.

This is immediately required.

Thanks so much in advance.

Kirandeep Singh
 
A

Andy B

Hi

You could try something like:
=SUMPRODUCT((A2:A1000="Abandoned")*(MONTH(B2:B1000)=1))
for Jan
=SUMPRODUCT((A2:A1000="Abandoned")*(MONTH(B2:B1000)=1))
for Feb etc
 
K

Kirandeep Singh

Thanks so much Andy

Andy B said:
Hi

You could try something like:
=SUMPRODUCT((A2:A1000="Abandoned")*(MONTH(B2:B1000)=1))
for Jan
=SUMPRODUCT((A2:A1000="Abandoned")*(MONTH(B2:B1000)=1))
for Feb etc


--
Andy.


month
Click to expand...
 
K

Kirandeep Singh

Thanks Andy/Amy,

=SUMPRODUCT((A2:A1000="Abandoned")*(MONTH(B2:B1000)=1)) is giving me #value
error :(

I have made desired changes like K2:K5000 for reason and I2:I5000 for month
(in some cells date format dd/mm/yy and in some of them it is mm/dd/yy)

Pls. advise.

Again thanking in advance.

Kirandeep Singh
 
A

Andy B

Ah

It sounds like your dates are not proper Excel dates, then.
You'll have to make them into Excel dates before the formula will work. One
way is to use a helper column with something like this in it:
=(LEFT(A2,2)&"/"&MID(A2,4,2)&"/"&RIGHT(A2,2))+0
This should coerce the cell into a date - which you should format as Date.
Once you are happy with this, you need to fix the values in place, so they
are not dependent on your original column any more. This involves copying a
blank unused cell, selecting your range of 'new dates' and Edit / Paste
Special . . Add
 
Top