Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum in range meeting criteria
hi,
i have a value in cell A10. i want to sum numbers in range B1:D8 where the value in A10 value is equal to a value in range A1:A9. how can this be done? thanks in advance, geebee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum in range meeting criteria
Hi,
I assume that A1:A9 is a typo and you mean A1:A8. Try this =SUMPRODUCT((A1:A8=A10)*(B1:D8)) Mike "geebee" wrote: hi, i have a value in cell A10. i want to sum numbers in range B1:D8 where the value in A10 value is equal to a value in range A1:A9. how can this be done? thanks in advance, geebee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum in range meeting criteria
hi,
this formula works, but how do i copy it down for other rows in the column. it looks like the other formulas i copying into the later rows are being offset. i want it to keep the same range it looks up in... just look at the value in A(rownumber). thanks in advance, geebee "Mike H" wrote: Hi, I assume that A1:A9 is a typo and you mean A1:A8. Try this =SUMPRODUCT((A1:A8=A10)*(B1:D8)) Mike "geebee" wrote: hi, i have a value in cell A10. i want to sum numbers in range B1:D8 where the value in A10 value is equal to a value in range A1:A9. how can this be done? thanks in advance, geebee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum in range meeting criteria
Use the Absolute reference to the range:
=SUMPRODUCT(($A$1:$A$8=$A10)*($B$1:$D$8)) The $ before one part of the address means it is an absolute reference and will not change when copied. In the $A10 shown above, the $A column will remain constant but the 10 (Row) will move + or - as you copy the formula up or down. HTH. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of rows between date range and meeting 2 string criteria | Excel Worksheet Functions | |||
FInding the largest number in a range meeting a criteria | Excel Discussion (Misc queries) | |||
calculate total items meeting criteria in range with multiple shee | Excel Worksheet Functions | |||
Sum within a date range meeting one other criteria | Excel Worksheet Functions | |||
Average of numbers within a range meeting certain criteria | Excel Worksheet Functions |