excluding column headers from formulas
 

In row 1, I have a column headers in text, such as Date, Time, Profit, Hours,
etc. Then all the following rows underneath have the corresponding data in
their specific format, such as date format, time format, currency format,
etc.

I have a bunch of formulas (mostly SUMIF and AVERAGEIF) where, for example,
if columnA (date) is February, then sum/avg columnB (profit). In the
formula, I use the whole column as a range (i.e. A:A) rather than a specific
range (i.e. A2:A100) as this list is indefinitely long. However, the column
headers in text (i.e. "Date" in text format) in row 1 of the column seems to
be affecting my formulas. Is there anyway to exclude this row of column
headers from the formulas? Can I change the column A, B, C to Date, Time,
Profit, etc?

excluding column headers from formulas
 

To answer the last part: no, you cannot alter the column headers. But many
people would like that!

But are the titles really influencing the results?
In A1 and B1 I entered some text
In A2:A20 I enters; 1,2,3,1,2,3.....
In B2:B20 I entered some random numbers
The formula =SUMIF(A:A,"<3",B:B) gave me the same result as I got using a
helper column.

best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"yowzers" wrote in message
...
In row 1, I have a column headers in text, such as Date, Time, Profit,
Hours,
etc. Then all the following rows underneath have the corresponding data
in
their specific format, such as date format, time format, currency format,
etc.

I have a bunch of formulas (mostly SUMIF and AVERAGEIF) where, for
example,
if columnA (date) is February, then sum/avg columnB (profit). In the
formula, I use the whole column as a range (i.e. A:A) rather than a
specific
range (i.e. A2:A100) as this list is indefinitely long. However, the
column
headers in text (i.e. "Date" in text format) in row 1 of the column seems
to
be affecting my formulas. Is there anyway to exclude this row of column
headers from the formulas? Can I change the column A, B, C to Date, Time,
Profit, etc?

excluding column headers from formulas
 

Thanks for the reply. For some reason, the titles are influencing the
results because when I delete the title, the formula works, but when I leave
the title in there, it does not. Specifically, I am using an AVERAGEIF
formula that looks like this:

=AVERAGE(IF(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1,She et1!$D:$D))

A1=Month in text (i.e. "JAN")
B1=Year in text (i.e. "2008")
Sheet1!D:D is in currency format but has the title "Profit" at the top.
Sheet1!A:A is in date format but has the title "DATES" at the top. If I
delete the text, the formula works.

"Bernard Liengme" wrote:

To answer the last part: no, you cannot alter the column headers. But many
people would like that!

But are the titles really influencing the results?
In A1 and B1 I entered some text
In A2:A20 I enters; 1,2,3,1,2,3.....
In B2:B20 I entered some random numbers
The formula =SUMIF(A:A,"<3",B:B) gave me the same result as I got using a
helper column.

best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"yowzers" wrote in message
...
In row 1, I have a column headers in text, such as Date, Time, Profit,
Hours,
etc. Then all the following rows underneath have the corresponding data
in
their specific format, such as date format, time format, currency format,
etc.

I have a bunch of formulas (mostly SUMIF and AVERAGEIF) where, for
example,
if columnA (date) is February, then sum/avg columnB (profit). In the
formula, I use the whole column as a range (i.e. A:A) rather than a
specific
range (i.e. A2:A100) as this list is indefinitely long. However, the
column
headers in text (i.e. "Date" in text format) in row 1 of the column seems
to
be affecting my formulas. Is there anyway to exclude this row of column
headers from the formulas? Can I change the column A, B, C to Date, Time,
Profit, etc?

.

excluding column headers from formulas
 

You are Not using AVERAGEIF which is an Excel 2007 function
You are using an array formula with a nested IF but it does not work as an
array
When I enter it as a non-array it averages all the D values regardless of
the values In A

This works for me
=SUMPRODUCT(--(TEXT(Sheet1!A1:A1000,"mmmyyyyy")=A1&B1),Sheet1!D1 :D1000)/SUMPRODUCT(--(TEXT(Sheet1!A1:A1000,"mmmyyyyy")=A1&B1))

It does not use full column formulas (with SUMPRODUCT this is allowed only
in Excel 2007+) but neither is it upset by empty cell
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"yowzers" wrote in message
...
Thanks for the reply. For some reason, the titles are influencing the
results because when I delete the title, the formula works, but when I
leave
the title in there, it does not. Specifically, I am using an AVERAGEIF
formula that looks like this:

=AVERAGE(IF(TEXT(Sheet1!$A:$A,"mmmyyyy")=A1&B1,She et1!$D:$D))

A1=Month in text (i.e. "JAN")
B1=Year in text (i.e. "2008")
Sheet1!D:D is in currency format but has the title "Profit" at the top.
Sheet1!A:A is in date format but has the title "DATES" at the top. If I
delete the text, the formula works.

"Bernard Liengme" wrote:

To answer the last part: no, you cannot alter the column headers. But
many
people would like that!

But are the titles really influencing the results?
In A1 and B1 I entered some text
In A2:A20 I enters; 1,2,3,1,2,3.....
In B2:B20 I entered some random numbers
The formula =SUMIF(A:A,"<3",B:B) gave me the same result as I got using a
helper column.

best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"yowzers" wrote in message
...
In row 1, I have a column headers in text, such as Date, Time, Profit,
Hours,
etc. Then all the following rows underneath have the corresponding
data
in
their specific format, such as date format, time format, currency
format,
etc.

I have a bunch of formulas (mostly SUMIF and AVERAGEIF) where, for
example,
if columnA (date) is February, then sum/avg columnB (profit). In the
formula, I use the whole column as a range (i.e. A:A) rather than a
specific
range (i.e. A2:A100) as this list is indefinitely long. However, the
column
headers in text (i.e. "Date" in text format) in row 1 of the column
seems
to
be affecting my formulas. Is there anyway to exclude this row of
column
headers from the formulas? Can I change the column A, B, C to Date,
Time,
Profit, etc?

.