SUM OF A RANGE

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

I HAVE USED THE FOLLOWING FORMULA TO COUNT THE NUMBER OF TIMES A VALUE
APPEARS BELOW 30000:

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000))

HOWEVER, I WOULD LIKE TO CALCULATE THE SUM OF THOSE VALUES BELOW 30000.

CAN ANYONE HELP WITH THIS?

THANKS
 
Dear Dave

Try the below

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),--([Query.xls]CLIENTS!D2:D8513))

If this post helps click Yes
 
Dear Dave

Try the below

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),--([Query.xls]CLIENTS!D2:D8513))

If this post helps click Yes
 
Hi Dave,
try

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),"),--([Query.xls]CLIENTS!D2:D8513))
 
Hi Dave,
try

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),"),--([Query.xls]CLIENTS!D2:D8513))
 
Thats excellent! Im sure Ive tried it. The one thing you have to be carefull
with is the formulas and filtering in the sheet your reading from. If you
keep these in or move anything the formula doesnt work.

But this is good! Thanks

Jacob Skaria said:
Dear Dave

Try the below

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),--([Query.xls]CLIENTS!D2:D8513))

If this post helps click Yes
---------------
Jacob Skaria


Dave said:
Hi,

I HAVE USED THE FOLLOWING FORMULA TO COUNT THE NUMBER OF TIMES A VALUE
APPEARS BELOW 30000:

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000))

HOWEVER, I WOULD LIKE TO CALCULATE THE SUM OF THOSE VALUES BELOW 30000.

CAN ANYONE HELP WITH THIS?

THANKS
Click to expand...
Click to expand...
 
Thats excellent! Im sure Ive tried it. The one thing you have to be carefull
with is the formulas and filtering in the sheet your reading from. If you
keep these in or move anything the formula doesnt work.

But this is good! Thanks

Jacob Skaria said:
Dear Dave

Try the below

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),--([Query.xls]CLIENTS!D2:D8513))

If this post helps click Yes
---------------
Jacob Skaria


Dave said:
Hi,

I HAVE USED THE FOLLOWING FORMULA TO COUNT THE NUMBER OF TIMES A VALUE
APPEARS BELOW 30000:

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000))

HOWEVER, I WOULD LIKE TO CALCULATE THE SUM OF THOSE VALUES BELOW 30000.

CAN ANYONE HELP WITH THIS?

THANKS
Click to expand...
Click to expand...
 
Thanks for the feedback...
--
If this post helps click Yes
---------------
Jacob Skaria


Dave said:
Thats excellent! Im sure Ive tried it. The one thing you have to be carefull
with is the formulas and filtering in the sheet your reading from. If you
keep these in or move anything the formula doesnt work.

But this is good! Thanks

Jacob Skaria said:
Dear Dave

Try the below

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),--([Query.xls]CLIENTS!D2:D8513))

If this post helps click Yes
---------------
Jacob Skaria


Dave said:
Hi,

I HAVE USED THE FOLLOWING FORMULA TO COUNT THE NUMBER OF TIMES A VALUE
APPEARS BELOW 30000:

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000))

HOWEVER, I WOULD LIKE TO CALCULATE THE SUM OF THOSE VALUES BELOW 30000.

CAN ANYONE HELP WITH THIS?

THANKS
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the feedback...
--
If this post helps click Yes
---------------
Jacob Skaria


Dave said:
Thats excellent! Im sure Ive tried it. The one thing you have to be carefull
with is the formulas and filtering in the sheet your reading from. If you
keep these in or move anything the formula doesnt work.

But this is good! Thanks

Jacob Skaria said:
Dear Dave

Try the below

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),--([Query.xls]CLIENTS!D2:D8513))

If this post helps click Yes
---------------
Jacob Skaria


Dave said:
Hi,

I HAVE USED THE FOLLOWING FORMULA TO COUNT THE NUMBER OF TIMES A VALUE
APPEARS BELOW 30000:

=SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000))

HOWEVER, I WOULD LIKE TO CALCULATE THE SUM OF THOSE VALUES BELOW 30000.

CAN ANYONE HELP WITH THIS?

THANKS
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

SUMIF Formula not working 1
Sumif as a fuction of If using multiple criteria 2
SUMPRODUCT not working? 1
EMBED VLOOKUP AND SIMPLE IF FORMULA IN A WORKBOOK 13
Sumproduct and wildcards 18
Conditional sum in a named range 8
Max and Min VBA Statement 9
Heavy data munching - is there a more efficient way of doing this? 8

Back
Top