If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 SUMIF with broken range
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## SUMIF with broken range

#11
August 23rd 12, 10:47 AM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster Posts: 637
SUMIF with broken range

PS.... I wrote:
> If we can rely on the "X" strings in column A, the following is the
> correct way for you to sum both positive and negative amounts.
> =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194>0),B4:Z194)
> =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194<0),B4:Z194)

[....]
> If we cannot rely on the "X" strings in column A, I would suggest that you
> insert a "helper" column with "X" strings. The helper column can be
> hidden.

It just occurred to me that the "X" strings probably represent people's
names, which you prudently edited out of the example file.

But a helper column with "X" strings is not needed. The following should
produce the same results:

=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194>0) ,B4:Z194)
=SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194<0) ,B4:Z194)

#12
August 23rd 12, 10:27 PM
 Wesler Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 5

Quote:
 Originally Posted by joeu2004[_2_] PS.... I wrote: > If we can rely on the "X" strings in column A, the following is the > correct way for you to sum both positive and negative amounts. > =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194>0),B4:Z194) > =SUMPRODUCT((LEFT(A4:A194,1)="x")*(B3:Z3="xc")*(B4 :Z194<0),B4:Z194) [....] > If we cannot rely on the "X" strings in column A, I would suggest that you > insert a "helper" column with "X" strings. The helper column can be > hidden. It just occurred to me that the "X" strings probably represent people's names, which you prudently edited out of the example file. But a helper column with "X" strings is not needed. The following should produce the same results: =SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194>0) ,B4:Z194) =SUMPRODUCT((A4:A194<>"")*(B3:Z3="xc")*(B4:Z194<0) ,B4:Z194)

I finally got what I wanted by doing this:
=SUMPRODUCT((A4:A194="X10")*(B3:Z3="xc")*(B4:Z194> 0),B4:Z194)
Having a formula for each name: X1, X2 &c.

But the thing is I have quarterly calendars which are set up exactly the same, whatever cell contains a name or label on one, does so in every other one, but it only works in the first sheet. This shouldn't be, should it?

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post How do I enter formula sum(range+range)*0.15 sumif(range>=3) tkw Excel Discussion (Misc queries) 2 October 1st 09 09:17 PM Dynamic Range broken salgud Excel Programming 2 April 14th 09 06:19 PM Broken: Set Cells in Range to 0 if blank ktoth04 Excel Programming 7 February 22nd 08 05:31 PM SUMIF - Range name to used for the "sum_range" portion of a SUMIF function Oscar Excel Worksheet Functions 2 January 11th 05 11:01 PM Deleting broken range names Ray Kanner[_2_] Excel Programming 1 February 17th 04 02:29 AM

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