I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

Hi,

In 2003 and earlier you can't reference the entire column for some
functions. change references like A:A to A1:A65000.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ColleenK" wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

Hi,

I'd do your formula like this:-

=SUMPRODUCT((H1:H20<=M7)*(H1:H20<"")*(Q1:Q20=A11) *(K1:K20))

You can't use full columns unless in 2007 and include a check for empty
cells in column H because an empty cell will evaluate as TRUE for <=M7

Mike

"ColleenK" wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

Thanks for the entire column note, unfortunately I am still getting an error.
--
CK


"Shane Devenshire" wrote:

Hi,

In 2003 and earlier you can't reference the entire column for some
functions. change references like A:A to A1:A65000.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"ColleenK" wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

I tried the formula the way you have shown it, now I get a #Value! error. I
am referencing another tab in the same workbook, would this cause problems?
--
CK


"Mike H" wrote:

Hi,

I'd do your formula like this:-

=SUMPRODUCT((H1:H20<=M7)*(H1:H20<"")*(Q1:Q20=A11) *(K1:K20))

You can't use full columns unless in 2007 and include a check for empty
cells in column H because an empty cell will evaluate as TRUE for <=M7

Mike

"ColleenK" wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

This syntax will ignore text in K1:k10.

=sumproduct(--(H1:H10<=m7),--(Q1:Q10=A11),K1:K10)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

ColleenK wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

--

Dave Peterson

Thanks Dave, the web site you supplied gave me the answer, thank you, thank
you, thank you
--
CK


"Dave Peterson" wrote:

This syntax will ignore text in K1:k10.

=sumproduct(--(H1:H10<=m7),--(Q1:Q10=A11),K1:K10)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

ColleenK wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK

--

Dave Peterson

Mike, your formula was correct, I miss-typed. Thank you so much for the help!
--
CK


"Mike H" wrote:

Hi,

I'd do your formula like this:-

=SUMPRODUCT((H1:H20<=M7)*(H1:H20<"")*(Q1:Q20=A11) *(K1:K20))

You can't use full columns unless in 2007 and include a check for empty
cells in column H because an empty cell will evaluate as TRUE for <=M7

Mike

"ColleenK" wrote:

I am trying to sum a column based on 2 sets of criteria, the first criteria
stipulates that the data being summed is <= a date, the second criteria
defines a name, when met it sums hours. The formula I used is:

=sumproduct((H:H<=m7),(Q:Q=A11),K:K)

Where m7 is a date & A11 is a name

It keep returning #NUM! please help
--
CK