Right now, I have a database in Sheet 1 by dates listed as 1/1/09, 2/15/09,
etc in column A and corresponding data in column B. Then I have a table on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would like
to put in a SUMIF function where I can get totals for column B on sheet 1 by
month IF the date in Sheet 1 column A equals the month listed in the column A
in sheet 2. Right now, the only way I can do this is by creating a column C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet 1?

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would
like
to put in a SUMIF function where I can get totals for column B on sheet 1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet
1?

Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I would
like
to put in a SUMIF function where I can get totals for column B on sheet 1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can put
this all into one formula so I don't have to have that column C in sheet
1?


.

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.

One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.



.

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I
can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.



.

By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as 1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc. I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in the
column A
in sheet 2. Right now, the only way I can do this is by creating a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway I
can
put
this all into one formula so I don't have to have that column C in
sheet
1?


.



.



.

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates
in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the
example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as
1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have
a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc.
I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in
the
column A
in sheet 2. Right now, the only way I can do this is by creating
a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where
it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway
I
can
put
this all into one formula so I don't have to have that column C
in
sheet
1?


.



.



.

And you'd like us to guess what the error message is? :-(
--
David Biddulph

"yowzers" wrote in message
...
By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates
in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the
example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as
1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have
a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc.
I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in
the
column A
in sheet 2. Right now, the only way I can do this is by creating
a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where
it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway
I
can
put
this all into one formula so I don't have to have that column C
in
sheet
1?


.



.



.

Perfect!

"T. Valko" wrote:

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates
in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the
example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as
1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have
a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc.
I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in
the
column A
in sheet 2. Right now, the only way I can do this is by creating
a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where
it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway
I
can
put
this all into one formula so I don't have to have that column C
in
sheet
1?


.



.



.



.

Last question, I promise.....maybe =P

I tried doing this same thing with the AVERAGEIF function and the answer is
coming back 0 for some reason. Am I doing something wrong?

"T. Valko" wrote:

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates
in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the
example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as
1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have
a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc.
I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in
the
column A
in sheet 2. Right now, the only way I can do this is by creating
a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where
it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway
I
can
put
this all into one formula so I don't have to have that column C
in
sheet
1?


.



.



.



.

I figured out the answer to the question below on my own. Now I need to add
a 0 function to both the SUMIF and AVERAGEIF functions as well as the MONTH
and YEAR

"yowzers" wrote:

Last question, I promise.....maybe =P

I tried doing this same thing with the AVERAGEIF function and the answer is
coming back 0 for some reason. Am I doing something wrong?

"T. Valko" wrote:

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan" in
text and B1 would say "2009" in text. Basically I want a sum if the dates
in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the
example
above, I want it to sort by Month, but how about if i want to sort by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as
1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I have
a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar, etc.
I
would
like
to put in a SUMIF function where I can get totals for column B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month listed in
the
column A
in sheet 2. Right now, the only way I can do this is by creating
a
column
C
in sheet 1 and having the date in column A turned into a month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function where
it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there anyway
I
can
put
this all into one formula so I don't have to have that column C
in
sheet
1?


.



.



.



.

I am using Excel 2007.
Now I need to add a 0 function

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),--(Sheet1!$B:$B0),Sheet1!$B:$B)

Array entered:

=AVERAGE(IF(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1,IF( Sheet1!$B:$B0,Sheet1!$B:$B)))

Even though you're using Excel 2007 you should still use as small a range as
possible. Both of those formulas will evaluate *EVERY* cell in the
referenced columns.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
I figured out the answer to the question below on my own. Now I need to
add
a 0 function to both the SUMIF and AVERAGEIF functions as well as the
MONTH
and YEAR

"yowzers" wrote:

Last question, I promise.....maybe =P

I tried doing this same thing with the AVERAGEIF function and the answer
is
coming back 0 for some reason. Am I doing something wrong?

"T. Valko" wrote:

Try this...

With Sheet1!A:A containing true Excel dates...
A1 = the month name as a TEXT entry = Jan
B1 = the year number = 2009

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1),Sheet1!$B:$B)

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
By not working, I mean I am getting an error message.

I am using Excel 2007.

Sheet1!$A:$A is full of dates in the DATE format. A1 would say "Jan"
in
text and B1 would say "2009" in text. Basically I want a sum if the
dates
in
column A:A are Jan AND 2009.

Thanks for your help.

"T. Valko" wrote:

I tried this formula and it doesnt seem to work.
=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

Specifically, what does "doesnt seem to work" mean?

One thing, unless you're using Excel 2007 you can't use entire
columns as
range references.

What's in A1?
What's in B1?

Are there any TEXT entries in Sheet!$A:$A? If so, the YEAR function
will
return an error.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
One more question. How about if I want another criteria? In the
example
above, I want it to sort by Month, but how about if i want to sort
by
Month
and Year? I tried this formula and it doesnt seem to work.

=SUMPRODUCT(--(TEXT(Sheet1!$A:$A,"mmm")=A1),--(YEAR(Sheet!$A:$A)=B1),Sheet1!$B:$B)

"T. Valko" wrote:

Try this array formula** :


=AVERAGE(IF(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1,Shee t1!$B$1:$B$150))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"yowzers" wrote in message
...
Awesome, this works great. How about for AVERAGEIF function
for the
same
criteria?

"Peo Sjoblom" wrote:

This would be easier to use


=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$150,"mmm")=A1),Sheet1!$B$1:$B $150)

change the cell references to fit your data then copy down





--


Regards,


Peo Sjoblom


"yowzers" wrote in message
...
Right now, I have a database in Sheet 1 by dates listed as
1/1/09,
2/15/09,
etc in column A and corresponding data in column B. Then I
have
a
table
on
Sheet 2 where column A is listed in text as Jan, Feb, Mar,
etc.
I
would
like
to put in a SUMIF function where I can get totals for column
B on
sheet
1
by
month IF the date in Sheet 1 column A equals the month
listed in
the
column A
in sheet 2. Right now, the only way I can do this is by
creating
a
column
C
in sheet 1 and having the date in column A turned into a
month by
using
=TEXT(A1,"mmm"). Then in sheet 2, I use the SUMIF function
where
it
sums
if
column C in sheet 1 equals column A in sheet 2. Is there
anyway
I
can
put
this all into one formula so I don't have to have that
column C
in
sheet
1?


.



.



.



.