#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Alectrical
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Alectrical
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Alectrical
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Alectrical
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Alectrical
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
Combine Indirect and Sumif dcd123 Excel Worksheet Functions 3 October 27th 05 04:20 PM
SUMIF with Mutiple Ranges & Criteria PokerZan Excel Discussion (Misc queries) 5 August 4th 05 10:31 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function Oscar Excel Worksheet Functions 2 January 12th 05 12:01 AM


All times are GMT +1. The time now is 02:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"