Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Hi
Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
=SUMPRODUCT(--(A2:A200="Apples"),--(C2:C200="Oranges"),D2:D200)
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Hi
Try something like this: =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges ")*(D2:D1000)) When using Sumproduct, make sure that the ranges are all the same size and are not full columns. Hope this helps. Andy. "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Thanks Bob
"Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="Apples"),--(C2:C200="Oranges"),D2:D200) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Thanks Andy, your function works on a new sheet, but when I type the
following function into an existing sheet I get the #VALUE message. Any ideas. =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H 13:H6000)) "Andy" wrote: Hi Try something like this: =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges ")*(D2:D1000)) When using Sumproduct, make sure that the ranges are all the same size and are not full columns. Hope this helps. Andy. "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Do you have text in H13:H6000? IF so, correct it or use my format.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Alectrical" wrote in message ... Thanks Andy, your function works on a new sheet, but when I type the following function into an existing sheet I get the #VALUE message. Any ideas. =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H 13:H6000)) "Andy" wrote: Hi Try something like this: =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges ")*(D2:D1000)) When using Sumproduct, make sure that the ranges are all the same size and are not full columns. Hope this helps. Andy. "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Hi
You must have a text value in your H range. Andy. "Alectrical" wrote in message ... Thanks Andy, your function works on a new sheet, but when I type the following function into an existing sheet I get the #VALUE message. Any ideas. =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H 13:H6000)) "Andy" wrote: Hi Try something like this: =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges ")*(D2:D1000)) When using Sumproduct, make sure that the ranges are all the same size and are not full columns. Hope this helps. Andy. "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Thanks Bob, you've been very helpful
"Bob Phillips" wrote: Do you have text in H13:H6000? IF so, correct it or use my format. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Alectrical" wrote in message ... Thanks Andy, your function works on a new sheet, but when I type the following function into an existing sheet I get the #VALUE message. Any ideas. =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H 13:H6000)) "Andy" wrote: Hi Try something like this: =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges ")*(D2:D1000)) When using Sumproduct, make sure that the ranges are all the same size and are not full columns. Hope this helps. Andy. "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF INDIRECT
Thanks again Andy, you've been very helpful
"Andy" wrote: Hi You must have a text value in your H range. Andy. "Alectrical" wrote in message ... Thanks Andy, your function works on a new sheet, but when I type the following function into an existing sheet I get the #VALUE message. Any ideas. =SUMPRODUCT((A13:A6000="IO")*(F13:F6000="2PLC")*(H 13:H6000)) "Andy" wrote: Hi Try something like this: =SUMPRODUCT((A2:A1000="Apples")*(C2:C1000="Oranges ")*(D2:D1000)) When using Sumproduct, make sure that the ranges are all the same size and are not full columns. Hope this helps. Andy. "Alectrical" wrote in message ... Hi Can anyone tell me how to sum a range of numbers in column D, provided that adjacent cells in row A contain "Apples" and row C contain "Oranges". Thanks Alec |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Combine Indirect and Sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |