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.

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.

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.



.

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.



.

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

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.



.



.

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.



.



.