Excel – Total Range of Values

Last update on November 6, 2009 04:56 AM by jak58
Published by jak58

Excel – Total Range of Values




Issue



I'm trying to get a range of values totalled.
I have one set of columns, let’s say column A1 to A200, ranging from -30 up to 1000 and another column adjacent (let’s say B1 to B200) with figures I need totalling
I want a formula to look at the first column A between the values-30 (MINUS 30) and 0 (ZERO) return the adjacent column B with a sum of what it found
Then following this in the next cell down I want it to look at the same criteria and show values between 1 and 30 and so on for selected ranges in column A.

Solution


Just use below formula ... for 0 to -100 which will add column B ...... 

=SUMPRODUCT((A1:A200<=0)*(A1:A200>=-100)*(B1:B200))


91 to 120 

=SUMPRODUCT(($A$1:$A$200<=120)*($A$1:$A$200>=91)*($B$1:$B$200))


Over 120 .... 

=SUMPRODUCT(($A$1:$A$200>120)*($B$1:$B$200))


And so on ........

Note


Thanks to mubashir azizfor this tip on the forum.
Best answers for « Excel – Total Range of Values » in :
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
They need your help