How to calculate text according to multiple months?
 

Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I have
another sheet to gather this data in which I'm putting the formula.

How to calculate text according to multiple months?
 

One way...

Assumes no empty cells in the data range.

=SUMPRODUCT(--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I have
another sheet to gather this data in which I'm putting the formula.

How to calculate text according to multiple months?
 

Well, I have empty cells, and the formula doesn't work.. The outcome of the
formula is 6, should be 3 (in my worksheet, not in the example, in my sheet
there are three hits, marked as "X" in the first three months.)

-Joona-

"T. Valko" wrote:

One way...

Assumes no empty cells in the data range.

=SUMPRODUCT(--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I have
another sheet to gather this data in which I'm putting the formula.



.

How to calculate text according to multiple months?
 

Assumes no empty cells in the data range.

There's is a typo in that. What I meant to say was:

Assumes no empty cells in the DATE range.

To account for that:

=SUMPRODUCT(--(ISNUMBER(A1:A20)),--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Well, I have empty cells, and the formula doesn't work.. The outcome of
the
formula is 6, should be 3 (in my worksheet, not in the example, in my
sheet
there are three hits, marked as "X" in the first three months.)

-Joona-

"T. Valko" wrote:

One way...

Assumes no empty cells in the data range.

=SUMPRODUCT(--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet
with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I
have
another sheet to gather this data in which I'm putting the formula.



.

How to calculate text according to multiple months?
 

On Thu, 18 Mar 2010 09:18:01 -0700, Joona
wrote:

Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I have
another sheet to gather this data in which I'm putting the formula.

=SUMPRODUCT(--(rng=Start),--(rng<=End),--(OFFSET(rng,0,1)="X"))

rng = the range of dates, e.g. A1:A100
Start = first date to be counted, or a cell reference containing that date
End = last date to be counted, or a cell reference countaining that date

If you have Excel 2007 or later, you could also use:

=COUNTIFS(rng,"="&Start,rng,"<="& End,OFFSET(rng,0,1),"X")

--ron

How to calculate text according to multiple months?
 

Thank you very much, that formula did the trick! just had to think for a
while how to translate that in to the finnish version of Excel... for
istance, commas (,) don't work in my version of finnish excel, I had to
replace them with ;'s to get it working. =)


"T. Valko" wrote:

Assumes no empty cells in the data range.

There's is a typo in that. What I meant to say was:

Assumes no empty cells in the DATE range.

To account for that:

=SUMPRODUCT(--(ISNUMBER(A1:A20)),--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Well, I have empty cells, and the formula doesn't work.. The outcome of
the
formula is 6, should be 3 (in my worksheet, not in the example, in my
sheet
there are three hits, marked as "X" in the first three months.)

-Joona-

"T. Valko" wrote:

One way...

Assumes no empty cells in the data range.

=SUMPRODUCT(--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet
with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I
have
another sheet to gather this data in which I'm putting the formula.



.



.

How to calculate text according to multiple months?
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Thank you very much, that formula did the trick! just had to think for a
while how to translate that in to the finnish version of Excel... for
istance, commas (,) don't work in my version of finnish excel, I had to
replace them with ;'s to get it working. =)


"T. Valko" wrote:

Assumes no empty cells in the data range.

There's is a typo in that. What I meant to say was:

Assumes no empty cells in the DATE range.

To account for that:

=SUMPRODUCT(--(ISNUMBER(A1:A20)),--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Well, I have empty cells, and the formula doesn't work.. The outcome of
the
formula is 6, should be 3 (in my worksheet, not in the example, in my
sheet
there are three hits, marked as "X" in the first three months.)

-Joona-

"T. Valko" wrote:

One way...

Assumes no empty cells in the data range.

=SUMPRODUCT(--(MONTH(A1:A20)<=3),--(B1:B20="x"))

--
Biff
Microsoft Excel MVP


"Joona" wrote in message
...
Hello!

I', having a slight problem with my Excel-worksheet. I have a sheet
with
data, like this:

A B
1.1.2010 X
2.2.2010 X
3.3.2010
4.4.2010 X


(A: DDMMYYYY)
How do I get Excel to count all the X:s in the first three months? I
have
another sheet to gather this data in which I'm putting the formula.



.



.