Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Nesting a sumproduct formula within a sumif formula.

Is this possible?

I want to write a formula that adds the values in column C, if the contents
of the corresponding cells in column A are less than a given value AND the
contents of the corresponding cells in column B contain a given text string.

Eg - Add up column C, if the cell in column A <20072 AND the cell in column
B contains "outlet".

Yesterday I got some great answers as to how to add up cells based on cells
containing a text string rather than equalling it, but now I need to nest
that within a sum if argument.

The formula I was given yesterday was....

=-SUMPRODUCT(ISNUMBER(SEARCH("outlet",B2:B100))*C2:C 100)

Is it possible to nest this within a sum if argument, or should I go about
this in a totally different way?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Nesting a sumproduct formula within a sumif formula.

=SUMPRODUCT(--(A2:A100<20072),--(ISNUMBER(SEARCH("outlet",B2:B100))),C2:C100)


--


Regards,


Peo Sjoblom



"jerrymcm" wrote in message
...
Is this possible?

I want to write a formula that adds the values in column C, if the
contents
of the corresponding cells in column A are less than a given value AND the
contents of the corresponding cells in column B contain a given text
string.

Eg - Add up column C, if the cell in column A <20072 AND the cell in
column
B contains "outlet".

Yesterday I got some great answers as to how to add up cells based on
cells
containing a text string rather than equalling it, but now I need to nest
that within a sum if argument.

The formula I was given yesterday was....

=-SUMPRODUCT(ISNUMBER(SEARCH("outlet",B2:B100))*C2:C 100)

Is it possible to nest this within a sum if argument, or should I go about
this in a totally different way?



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Nesting a sumproduct formula within a sumif formula.

WOW.

Thanks.

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(A2:A100<20072),--(ISNUMBER(SEARCH("outlet",B2:B100))),C2:C100)


--


Regards,


Peo Sjoblom



"jerrymcm" wrote in message
...
Is this possible?

I want to write a formula that adds the values in column C, if the
contents
of the corresponding cells in column A are less than a given value AND the
contents of the corresponding cells in column B contain a given text
string.

Eg - Add up column C, if the cell in column A <20072 AND the cell in
column
B contains "outlet".

Yesterday I got some great answers as to how to add up cells based on
cells
containing a text string rather than equalling it, but now I need to nest
that within a sum if argument.

The formula I was given yesterday was....

=-SUMPRODUCT(ISNUMBER(SEARCH("outlet",B2:B100))*C2:C 100)

Is it possible to nest this within a sum if argument, or should I go about
this in a totally different way?




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
sumif/sumproduct formula help Robert Excel Worksheet Functions 2 May 30th 07 04:40 AM
sumproduct or sumif formula help ferde Excel Discussion (Misc queries) 4 April 15th 07 04:36 AM
Excel : Nesting of functions such as sumproduct and sumif Nimish Shah Excel Worksheet Functions 6 December 22nd 06 02:27 PM
Nesting of sumif formula. ramana Excel Worksheet Functions 2 January 2nd 06 01:00 PM
SumIf/SumProduct Formula Help Jacinthe Excel Worksheet Functions 2 March 10th 05 11:37 PM


All times are GMT +1. The time now is 03:25 PM.

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"