SumProduct

C

ColleenK

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
 
S

Shane Devenshire

Hi,

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

Mike H

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
 
C

ColleenK

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?
 
D

Dave Peterson

C

ColleenK

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top