SUMPRODUCT and INDIRECT - 39143

Can anyone tell me why the following doesnt work?

=SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))

where XN01 is a staff salary band
$CI:$CI is a headcount figures containing 1's and 0's
$BL is an Ethnicity grouping which I need to group up e.g. A = white 
english, B = white welsh. I am basically summing the headcount of white 
people at band XN01.

Some of the problems I am having is that if I leave just one criteria for 
ethnicity in e.g. "A", then the formula works. I then have to recreat the 
calculation for bands XN01-XN09, with twelve other groupings of ethnicity so 
I need to group many codes together.
Thanks
0
Utf
1/15/2010 4:26:09 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
594 Views

Similar Articles

[PageSpeed] 9

See my earlier post

HTH

Bob

"JPDS" <JPDS@discussions.microsoft.com> wrote in message 
news:3A5F2B3E-5F2F-4306-8716-5DE056B7ABCB@microsoft.com...
> Can anyone tell me why the following doesnt work?
>
> =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
>
> where XN01 is a staff salary band
> $CI:$CI is a headcount figures containing 1's and 0's
> $BL is an Ethnicity grouping which I need to group up e.g. A = white
> english, B = white welsh. I am basically summing the headcount of white
> people at band XN01.
>
> Some of the problems I am having is that if I leave just one criteria for
> ethnicity in e.g. "A", then the formula works. I then have to recreat the
> calculation for bands XN01-XN09, with twelve other groupings of ethnicity 
> so
> I need to group many codes together.
> Thanks 


0
Bob
1/15/2010 4:32:57 PM
Hi Bob,

Ive tried your formula in a blank sheet and it works fine. I just cant get 
it to work in my sheet. Is it worth me sending you a copy? Maybe i'm trying 
to get it to do something which it cant do.

"Bob Phillips" wrote:

> See my earlier post
> 
> HTH
> 
> Bob
> 
> "JPDS" <JPDS@discussions.microsoft.com> wrote in message 
> news:3A5F2B3E-5F2F-4306-8716-5DE056B7ABCB@microsoft.com...
> > Can anyone tell me why the following doesnt work?
> >
> > =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
> >
> > where XN01 is a staff salary band
> > $CI:$CI is a headcount figures containing 1's and 0's
> > $BL is an Ethnicity grouping which I need to group up e.g. A = white
> > english, B = white welsh. I am basically summing the headcount of white
> > people at band XN01.
> >
> > Some of the problems I am having is that if I leave just one criteria for
> > ethnicity in e.g. "A", then the formula works. I then have to recreat the
> > calculation for bands XN01-XN09, with twelve other groupings of ethnicity 
> > so
> > I need to group many codes together.
> > Thanks 
> 
> 
> .
> 
0
Utf
1/18/2010 3:24:02 PM
Yeah sure, mail it to Bob dot NGs at gmail dot com (do the obvious)

Bob


"JPDS" <JPDS@discussions.microsoft.com> wrote in message 
news:2E378FAC-2DFF-4A16-A9E4-B2AEF0CF4AE3@microsoft.com...
> Hi Bob,
>
> Ive tried your formula in a blank sheet and it works fine. I just cant get
> it to work in my sheet. Is it worth me sending you a copy? Maybe i'm 
> trying
> to get it to do something which it cant do.
>
> "Bob Phillips" wrote:
>
>> See my earlier post
>>
>> HTH
>>
>> Bob
>>
>> "JPDS" <JPDS@discussions.microsoft.com> wrote in message
>> news:3A5F2B3E-5F2F-4306-8716-5DE056B7ABCB@microsoft.com...
>> > Can anyone tell me why the following doesnt work?
>> >
>> > =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
>> >
>> > where XN01 is a staff salary band
>> > $CI:$CI is a headcount figures containing 1's and 0's
>> > $BL is an Ethnicity grouping which I need to group up e.g. A = white
>> > english, B = white welsh. I am basically summing the headcount of white
>> > people at band XN01.
>> >
>> > Some of the problems I am having is that if I leave just one criteria 
>> > for
>> > ethnicity in e.g. "A", then the formula works. I then have to recreat 
>> > the
>> > calculation for bands XN01-XN09, with twelve other groupings of 
>> > ethnicity
>> > so
>> > I need to group many codes together.
>> > Thanks
>>
>>
>> .
>> 


0
Bob
1/19/2010 12:00:07 AM
Reply:

Similar Artilces:

SUMPRODUCT with Date?
I'm trying to use the formula below, but its not working correctly. On the Hiring tab, column T has a date formatted as MM/DD/YYYY. I want a formula that counts the number of hires in a quarter with other criteria. So for instance if column U = A3 (Cons) and column D = GMT and column T is in 1Q (Jan thru March). I'll also need the same formula for 2Q (April thru June), 3Q & 4Q. =SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),--(MONTH(Hiring!T1:T500<=3))) A3=Cons B2=GMT Try this... =SUMPRODUCT(--(Hiring!U1:U500=A3),--(Hiring!D1:D500=B2),-...

SumProduct/SUMIF? #2
the formula is still returning all zeroes..... is this the best way to do this -- kkondrat ----------------------------------------------------------------------- kkondrat1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=600 View this thread: http://www.excelforum.com/showthread.php?threadid=26650 Hi so also your original formula returns zero? -- Regards Frank Kabel Frankfurt, Germany "kkondrat1" <kkondrat1.1do7by@excelforum-nospam.com> schrieb im Newsbeitrag news:kkondrat1.1do7by@excelforum-nospam.com... > > the formula is still retur...

SUMPRODUCT #15
Can anyone tell me why this formula is giving me a fraction for the result: =SUMPRODUCT(--(J5:J23>=0.95),--(J5:J23<1.05),--(J5:J23))*100/ COUNTA(J5:J23) J5:J23 are all percentages. J5 value is 96.67% the rest are all 100% so I was expecting a result from the formula above of 100% instead I get 99.82%. When I check the formula out I see the SUMPRODUCT returns 18.966666667 rather than 19 that I would have expected. Regards Peter On Jun 11, 3:09=A0pm, Pete <fell-wal...@hotmail.co.uk> wrote: > Can anyone tell me why this formula is giving me a fraction for the > result: >...

INDIRECT reference
Hi. I need to have in say cell A1 a drop down list (which I obtain with the data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so that people looking at the spreasheet can select their % desired. BUT, while I would like the numbers to chosse from in the drop down list to be those above, I really need them to be divided by 12 i.e. (0%/12), then (5%/12), then (10%/12), etc. I thought i cd try to do it with the indirect function but I ma not really sure what the best way to do it. Is? Any suggestions? I made be going about it the wrong way as well.... Tx, Sat Sat, Can't...

Excel 2003
I'm simply brain dead this morning and could use a little help. I have assembled a column of pointers into a table. These pointers are simply the row number of the selected data in a table. Assuming that this column starts in A01, I would like to create a formula in B01 that simply makes B01 equal to the data located in a specified column (use column F for the example) in the row specified by A01. I remember doing this before, but it just excapes me this morning. Thanks in advance for your consideration and help, Craig =indirect("F"&a1) Craig Brandt wrote: > ...

Sumproduct a column where 2 adj text columns contain same value
Hi, I'm using a sumproduct formula to ascertain the number of times that a value between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell in column B (B3:B26) contains either "Smith", "Draper" or "Jones": =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"})) This formula works fine and details the number of entries where the value in column A is between 0 and 1, and the name in the adjacent column B cell is Smith Draper or Jones. What I need to do is the same SUMPRODUCT of 0-1...

Indirect function
Hi, I struggle to create a macro with INDIRECT function that would jump to different cells. What cell I want to jump to depends on a currently selected cell. This means I need to put a relative reference into the function. Here is my function - I need to replace R1C1 expression with a realtive one (currently selected cell address with the same behavior as R1C1 expression). How can I do that? Application.Goto Reference:= _ "INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56 C8,1)),false)))" Whatever I do I get follo...

Sumproduct Question
=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)) This above formula achieves the desired result. However, when I add a fourth column (below) I get a Value error. I think I'm close...any ideas? =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000)) First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too. Second, do you have any text values in M25:M2000--or any errors in that range? If you have some text, you can use a different formula to ignore text: =SUMPRODUCT(--($E$25:$E$2000=4), --($F$25:$F$2000=6.5), ...

Type mismatch error using Sumproduct
I've searched through similar topics and tried several variations of code, but I keep getting a type-mismatch error when trying to use Sumproduct. I am wanting to use this in several parts of my code. The first instance (which is also where the error keeps occurring) is below. With Worksheets("Hours") TotalHrs = .Evaluate("SUMPRODUCT((HoursCodes=ClassCode)* _ (HourStat=CliStat)*PeriodHours)") End With TotalHrs is dim'd as Single, ClassCode as String (either 4 or 5 characters long), CliStat as Integer (0, 1, or 99), and the rest as R...

SumProduct using greater than
I have the following formula =SUMPRODUCT(('All Resource Plan'!$B$3:$B$42=Principal)* ('All Resource Plan'!D$3:D$42>0)) Where B3 to B42 on the all resource plan tab have a variety of roles including principal. On the same tab d3 to d42 is a range of numbers from -1 to +1. I need to sum all values associated with a role that are greater than 0. I was using this formula with an =1 and it worked fine, however when I changed it to >0 it no longer works. Any help would be appreciated. First of all, you are counting not summing, secondly the formula works for me....

Adding to a SUMPRODUCT formula
Afternoon all I have the below forumula =(SUMPRODUCT((B22=1)*(F22="EX"),1))+(SUMPRODUCT((B22=1)*(F22="CURRENT"),2))+(SUMPRODUCT((B22<>1)*(F22="CURRENT"),B22))+(SUMPRODUCT((B22<>1)*(F22="EX"),B22)) However if there is nothing in cell B22 I am getting VALUE appear. So I need to add to the above formula that if there is nothing in B22 to simply put nothing in the cell Thank you in advance Hi Mark Try =IF(B22="","",(SUMPRODUCT((B22=1)*(F22="EX"),1)) +(SUMPRODUCT((B22=1)*(F22="CURRENT"...

Indirect concatenated link doesn't work with UNC
Excel 2003 SP3. ARRRGGGHHHH! I need to sum multiple cells in workbook A into one cell in workbook B, where part of path to workbook A is a variable input by the user. If I use =INDIRECT("'x:\folder\folder\"&input&"\[filename.xls]worksheet_name'!cell")+INDIRECT(same thing, different cell), all is well. If I change x: to the UNC path (\\server\sharename, and I know I have the right server and share names) I get #REF! error. Workbook A is already open in the same instance of Excel. To make it even more frustrating, if I copy the cell that doesn't wo...

Sum Indirect
Hi Struggling with this indirect formula - got #ref error exel 2003 A4 = sheet number =SUM(INDIRECT("&A4&!"&M1:M14)) any one able to assist? regards -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-functions/201004/1 Try =SUM(INDIRECT(A4&"!M1:M14")) -- HTH Bob "BNT1 via OfficeKB.com" <u19326@uwe> wrote in message news:a61f79aa03a25@uwe... > Hi > > Struggling with this indirect formula - got #ref error > > exel 2003 > A4 = sheet number > &g...

sumproduct problem
hello, i've been reading up on all the sumproduct related posts, but can't figure out what's wrong with mine... this is my formula: =SUMPRODUCT(Détail!B2:B151=1;Détail!AB2:AB151=1;Détail!T2:T151) when i look at the insert function arguments box to help define the arrays, excel returns the right values, i.e.: for array 1 : false, true, false, false,.. (this is the first condition) for array 2 : true, true, true, true,... (this is the second condition) for array 3 : 1, 2, 4, 12, 4, 5,... (these are the values to add up) so i thought excel would return '2'...

Sumproduct Question #3
=SUMPRODUCT(--(H15:H3000=B9)*(G15:G3000=C9),(l15:l3000)) This forumula doesn't quite do what I want. As it stands it sums the values in l15:l3000. What I want it to do is count the number of values in l15:l3000 providing the criteria is met in the first part of the forumula. I'm not quite sure how to do that. (l15:l3000 will have some blank cells if that matters.) Hi Jim, Am Fri, 20 Aug 2010 10:48:40 -0700 schrieb JimS: > What I want it to do is count the number of > values in l15:l3000 providing the criteria is met in the first part of > the forumula. =SUMPRODUCT(--...

Sumproduct Question #1
I have been doing a lot of reading on this function and can usually get it to work to do the job - not overly complex. One thing that is still not understood (and not the only thing) is what is the difference between using * or , to separate the arrays in the argument? Kevin Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html The bad news is that Bob's site is experiencing problems. You may want to try every so often to see if it's back up. Kevin wrote: > > I have been doing a lot of reading on this function an...

Struggling with Sumproduct
Hi Reposting as still and issue. I have a spreadsheet with accounts transaction data where column a = date, column b = nominal code, Column c = department name, column d = department number, column e = amount. I need to query the columns to sum transactions for a particular month for a particular nominal code, for a particular department. The department name is only included to identify the department number, and will not be used. My initial thoughts are =SUMPRODUCT((Sheet1!A2:A16202=MONTH(6))*(Sheet1!B2:B16202="4000")*(Sheet1!D2:D16202=1)*(Sheet1!E2:E16202)) However it re...

Sumproduct or ?????
Hi All, I have 2 tables one is cust table and the other one is amount table. A B C D Customer Table Amount Table Parent ID Cust # Cust # Amount 2001 AA AA 5 2001 BB AA 5 2001 CC AA 5 2001 DD AA 5 2001 EE AA -5 2001 FF AA -5 2001 GG BB 3 BB 3 BB -3 Summary AA - I want to be able to count if "AA" in Cust table (Column B) then count positive amount minus negative amount in amount table (Column D). In this case the answer is "2" BB - The same thing with "BB". The answer is "1" Thank you ...

excel
Is it possible to nest an INDIRECT function inside of a VLOOKUP? Here's what I've got so far: Cell A27: 6-23-2004 Cell B27: =VLOOKUP(B$4,'6-23-2004'!$A$1:$C$70,3,FALSE)/60/60/24 I want to replace '6-23-2004' with INDIRECT($A27) so I can autofill this formula across a large worksheet, and so when I adjust the date range, it will dynamically update the information PROPERLY according to the name (row 4 of same col) and date (col A of same row, referring to a separate worksheet in the same workbook by that name) in the formula. I can't seem to get the syntax righ...

Using SumProduct in VB
I was hoping someone can help me. I am writing a script that parses though an excel file and extract the unique entries and sums the quantities with respect to certain criteria (ie Month or Year). I currently have an excel formula that does what I am looking for, but I need to convert it to a VB script. The formula looks like: =SUMPRODUCT(($C$2:$C$30="boxes")*($E$2:$E$30="January")*($D$2:$D$30)) Where C2:C30 contains the item, E2:E30 is the month, and D2:D30 contains the quantity of the items. So this example counts the number of boxes from January in this data r...

Sumproduct Problem (AB)
Hi everyone, can you please sort out this problem for me? SUMPRODUCT(('Client Opening Stock'!B2:B138=$A$1)*('Client Opening Stock'!C2:AH138=A7),'Client Opening Stock'!C2:X138) 'Client Opening Stock'!B2:B138 is the Client Name Client Opening Stock'!C2:AH138 is the Product 'Client Opening Stock'!C2:X138 is the Amount I belive the formula is not the proper formula, but this is the result I need. Thank you for your kind help You need to tell us what the problem is, and what solution you want. Why is it "not the proper formula...

INDIRECT function error
Hi I have a strange problem that I can't work out. In sheet 1 A1 - A10, I have a list of Surnames. Sheet 2 to sheet 11 are named according to this list. (all these sheets have been copied from a Template sheet) If I use =INDIRECT(a1&"!D20") it doesn't pull the value from the relevant sheet. One thing I noticed is that if I manually insert a sheet and reference the name in the INDIRECT function, the formula works. It doesn't seem to work for the sheets that have been copied Any ideas? Hi Anthony, > If I use =INDIRECT(a1&"!D20") it doesn&#...

SUMPRODUCT() problem
Hi! I try to get this formula or someting like it to work. =SUMPRODUCT((M17:M1115="245-B")*(R17:R1115="Not OK")*(H17:H1115=F2)) The problem is in the H-Column. It contains Dates and times like for instance 26-04-2007 11:36:34 F2 in the formula contains 26-04-2007, and this is where the problem arises. No values in H-column contains this exact value, so the formula retunrs a zero. I have tried using VALUE() combined with INT(), and other combinations on the h-column, but with no result aso far. How should my formula look if I want to calulate the number of te...

SUMPRODUCT not working?
Hi I need to sum a cell range (F2:F90) based on two conditions of two other cell ranges (D2:D90="XXX" and I2:I90=""), where the parameter of the first condition may change ("XXX" changes to "MFB", "TGDolfa", "GATV", etc.) and the second remains constant (I2:I90=""). On one cell I tried the formula: =SUMPRODUCT((D2:D90="MFB")*(I2:I90="")*(F2:F90)) and it worked as I got an accurate result. Now the part that is driving me crazy... When I copied the formula to the cell below and changed the first...

sumproduct between 2 ranges
I want to count the number of cells whose values fall between 0.40 and 0.61. I did =SUMPRODUCT((M3:M20>0.4)-(M3:M20<0.61)) but the result is not the same as when I manually count the number of cells so the formula is clearly wrong. how do I fix? -- Message posted via http://www.officekb.com One way: =SUMPRODUCT(--(M3:M20>0.4),--(M3:M20<0.61)) =sumproduct() likes to work with numbers. The -- changes true/false to +1/0. "Patty via OfficeKB.com" wrote: > > I want to count the number of cells whose values fall between 0.40 and 0.61. > I did > > ...