| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM OF A RANGE
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM OF A RANGE
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" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM OF A RANGE
Hi Dave,
try =SUMPRODUCT(--([Query.xls]CLIENTS!F2:F8513="Yes"),--([Query.xls]CLIENTS!D2:D8513<30000),"),--([Query.xls]CLIENTS!D2:D8513)) "Dave" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM OF A RANGE
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" wrote: 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" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM OF A RANGE
Thanks for the feedback...
-- If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: 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" wrote: 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" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
| Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
| Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Linear Mode
