excluding column headers from formulas

[yowzers]

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?

 

[Bernard Liengme]

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

 

[yowzers]

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,Sheet1!$D:$D))

A1=Month in text (i.e. "JAN")
B1=Year in text (i.e. "2008")
Sheet1!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]

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!D11000)/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