SUMPRODUCT 01-21-10

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

However, I cant seem to get the following to work: 
=SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))

I need to be able to summarise (using a headcount indicator (1) in Column 
CI) groups of people together who are in certain groups e.g. staff can be in 
groups A,B or C in Column BL, and in groups XN01 or XR01 in column O.

The indirect function is used as there are monthly named sheets with similar 
data in.

Thanks


0
Utf
1/21/2010 5:32:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
819 Views

Similar Articles

[PageSpeed] 29

Try it like this...

=SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A","B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000"))

-- 
Biff
Microsoft Excel MVP


"JPDS" <JPDS@discussions.microsoft.com> wrote in message 
news:E7E1042E-4C11-4A3F-AA5A-9C9C600E8885@microsoft.com...
> The following formula works fine:
> =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
>
> However, I cant seem to get the following to work:
> =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
>
> I need to be able to summarise (using a headcount indicator (1) in Column
> CI) groups of people together who are in certain groups e.g. staff can be 
> in
> groups A,B or C in Column BL, and in groups XN01 or XR01 in column O.
>
> The indirect function is used as there are monthly named sheets with 
> similar
> data in.
>
> Thanks
>
> 


0
T
1/21/2010 5:56:56 PM
That works perfectly, now I have the arduous task of understanding why your 
formula works and mine doesnt! What was wrong with my formula so I can 
understand it a bit more?

Thanks again

"T. Valko" wrote:

> Try it like this...
> 
> =SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A","B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000"))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "JPDS" <JPDS@discussions.microsoft.com> wrote in message 
> news:E7E1042E-4C11-4A3F-AA5A-9C9C600E8885@microsoft.com...
> > The following formula works fine:
> > =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
> >
> > However, I cant seem to get the following to work:
> > =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
> >
> > I need to be able to summarise (using a headcount indicator (1) in Column
> > CI) groups of people together who are in certain groups e.g. staff can be 
> > in
> > groups A,B or C in Column BL, and in groups XN01 or XR01 in column O.
> >
> > The indirect function is used as there are monthly named sheets with 
> > similar
> > data in.
> >
> > Thanks
> >
> > 
> 
> 
> .
> 
0
Utf
1/25/2010 11:14:01 AM
See if this helps:

http://xldynamic.com/source/xld.SUMPRODUCT.html

If not, just let me know and I'll give you a "deluxe" explanation!

-- 
Biff
Microsoft Excel MVP


"JPDS" <JPDS@discussions.microsoft.com> wrote in message 
news:3E65894E-2957-4885-9501-031398DC3D0B@microsoft.com...
> That works perfectly, now I have the arduous task of understanding why 
> your
> formula works and mine doesnt! What was wrong with my formula so I can
> understand it a bit more?
>
> Thanks again
>
> "T. Valko" wrote:
>
>> Try it like this...
>>
>> =SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A","B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000"))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "JPDS" <JPDS@discussions.microsoft.com> wrote in message
>> news:E7E1042E-4C11-4A3F-AA5A-9C9C600E8885@microsoft.com...
>> > The following formula works fine:
>> > =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
>> >
>> > However, I cant seem to get the following to work:
>> > =SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(INDIRECT($AW$3&"!$CI$1:$CI$6000")))
>> >
>> > I need to be able to summarise (using a headcount indicator (1) in 
>> > Column
>> > CI) groups of people together who are in certain groups e.g. staff can 
>> > be
>> > in
>> > groups A,B or C in Column BL, and in groups XN01 or XR01 in column O.
>> >
>> > The indirect function is used as there are monthly named sheets with
>> > similar
>> > data in.
>> >
>> > Thanks
>> >
>> >
>>
>>
>> .
>> 


0
T
1/25/2010 5:12:47 PM
Reply:

Similar Artilces:

IF Statement 02-17-10
The statement below is working good. I need to add a statement to the end that all other numbers will be 200%. Can someone help, please. =(IF(G6=20,"110%",(IF(G6=25,"110%",(IF(G6=50,"110%") Thank you Tina Try this nut note a couple of things, There are no a lot less parenthesis, yo had too many Ive removed the quites from the percent answers. Format your formula cell as percent with as many or as few decimal places as you want If the formula works for you then that's fine but it 'could' produce unwanted results. For example ...

Failed Updates 06-26-10
Usually when I try to install updates, a message will appear indicating that the process has failed. When I allow Windows to automatically install updates, however, my Internet connection fails and I then have to do a system restore to remove the updates so that I can use the Internet again. Can you please help me with this, as I do want to be able to update my computer with programs and anti-virus updates without losing my Internet connection each time? Thanks. Allen • Which version of the Windows operating system am I running? http://windows.microsoft.com/en-us/wi...

auto play 03-26-10
I use an HP pc with Windows Vista 64-bit home premium. I like to play DVDs on the PC using Cyberlink PowerDVD. I can not get the pc to autoplay DVD files when they are inserted. When I use the Control Panel to open the "Set AutoPlay settings," PowerDVD does not show up in the drop-down menu of choices? Can someone tell me please how to make that happen? Thanks "D Van Deusen" <lucasfalls@hotmail.com> wrote in message news:E84FB90C-FC0B-42FC-868C-50B59F9BF8A6@microsoft.com... > I use an HP pc with Windows Vista 64-bit home premium. I like to play...

how do I change the default font size in endnotes? 02-28-10
How do I alter the default font size in endnotes? I've been qable to change the default typeface but the font size reverts to 10-point each time I create a new endnote. Endnote is a Style that you can modify. -- Rae Drysdale "kar201245" wrote: > How do I alter the default font size in endnotes? I've been qable to change > the default typeface but the font size reverts to 10-point each time I create > a new endnote. Did you change it by modifying the Envelope Return and Envelope Address paragraph styles? -- Suzanne S. Barnhill Microsoft ...

Windows Calendar 05-15-10
I'm using Vista Home Premium. The calendar used to work---that is, it used to remind me about appointments. It no longer does. It will only remind me when the calendar is open. File/Options has "Reminders should show when Windows Calendar not running" checked. Windows Calendar is in the Startup menu. I understand this is a very popular problem but I can't find answers---only people complaining about it no longer working. Can someone direct me to the right source to get this issued resolved? Thank you. Connie ...

Upgrade to 10
Frustation building...I'm trying to upgrade to 10.0 from 8.0 SP5 in a test environment on a Virtual machine. When it tries to install the components it fails on the MS Sql Server native client 9, MS Dexterity Shared Components 10, and the MS Application Error reporting 11. It tells me to look at the .tmp file for information but I can't find the stupid .tmp file. I'm running the upgrade from the DVD on my local machine and connecting to the test server via Remote Desktop Connection. Any help out there???? -- jav If you have access to Customer/Partner Source s...

Garnishment in GP 10
Hi All, I was wondering if there is a way to create a invoice in Payables based on when a garnishment is taken out during payroll? I have a client that is tracking the garnishment and who it needs to be paid to on excel. If there is a way for GP to generate an invoice in Payables after each payroll based on each garnishment that would be a huge selling point for them. Any information would be very helpful. -- TC On May 14, 5:26=A0pm, TC <T...@discussions.microsoft.com> wrote: > Hi All, > > I was wondering if there is a way to create a invoice in Payables based on= >...

Simple query question 08-05-10
Hi, I'm using SQL Server 2005. What I want is to return a column "venue", but if that column is null, I want to return the value of the column "site" (in either case, I want the column header to be "venue"). How do I construct such a query? Thanks, - Dave You can use expression with COALESCE and alias the expression as venue: SELECT COALESCE(venue, site) AS venue... -- Plamen Ratchev http://www.SQLStudio.com Try this: SELECT COALSECE(venue, site) AS venue FROM my_table -- Gert-Jan laredotornado wrote: > > Hi, &...

Record Locking 03-17-10
For a Ms Access 2003 Db, Split, multi-user (10 users or so) in a peer-2-peer environment. I read that it can be beneficial to remove record locking on the forms and am confused?! I thought edited record was what should be setup. Why not? If you remove it, then how are updates managed in the rare even 2 user work on the same record? Thank you for the clarifications. QB "QB" <QB@discussions.microsoft.com> wrote in message news:65FEF730-D717-41C8-8ECA-C0D3A7A1B8DE@microsoft.com... > For a Ms Access 2003 Db, Split, multi-user (10 users or so) in a &g...

Love Potion for Miss Blandish #10
yada yada hi darmasya, glanil,bavati bharata, agydanam adarmasya, kridadmanam padarmasya, ...

SUMPRODUCT and INDIRECT
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 crite...

Text Boxes #10
Hi I've nearly finished a publication but have now been told that I need to put a side panel on every page. This means that I need to move and resize all the text boxes in the whole document!! Is there a way of moving them all together as they need to be the same size and in the same place on each page? I don't know of a way to do all the pages in one operation. What you can do to make all the existing text boxes the same size by temporarily move the guide over to a location to match the intended location of the side of the text box, go through the pages "snapping" ...

duplicates #10
I need to specifically check for duplicates between two columns: For example: col1 col2 12345 1102897 4794848974 8347474 493836 1102897 What formula can I use to check the data from col 2 against the data from col 1? =IF(COUNTIF($A:$A,B1)>0,"Duplicate","") and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Danbmarine" <Danbmarine@discussions.microsoft.com> wrote in message news:ADFFA886-6ECE-4609-97F9-0F35BE95C5A9@microsoft.com... > I need to specifi...

email 04-04-10
i can receive emails but cant send them comes up non-local ip address server error 554 error number 0x800ccc6f only had this problem since changeing over to talk talk -- nigeldebi Posted via http://www.vistaheads.com That's not the complete error message. Do you get an error message when you try to send or receive? If so,=20 right-click on your error message, copy, then paste it into a reply = here. We can't do much troubleshooting without the complete error message.=20 --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.micro...

Generating Excel XML with embedded char(10)s
I have a script that generates an Excel file in XML. Everything is working wonderfully except for one column where I want to embed line feeds in the cells. It appears that the characters are there because when I view the XML file in a text editor, the contents of those cells are on separate lines but when I view the file in Excel, the line feed character seems to be replaced with a single space. So, for instance, I have: <Row> <Cell ss:StyleID="Default"><Data ss:Type="String">ABC Corp</Data></ Cell> <Cell ss:StyleID="Default">...

Format Date 05-28-10
Hi, all, How do I format the date to show May, 2010? The closest I can get is May-10 from the drop down list. Thanks, Malcolm Malcolm wrote: > Hi, all, > How do I format the date to show May, 2010? The closest I can get is May-10 > from the drop down list. > > Thanks, > Malcolm Custom date format: mmmm, yyyy Custom Format mmmm, yyyy Gord Dibben MS Excel MVP On Fri, 28 May 2010 09:05:01 -0700, Malcolm <Malcolm@discussions.microsoft.com> wrote: >Hi, all, >How do I format the date to show May, 2010? The closest I can get is M...

error message in outlook #10
Hi, When I click on a link within an email I get an error message "the operation had been cancelled due to restrictions in effect on this computer." How can I change that so I can open links directly from emails? Thanks Chagit "Chagit" <Chagit@discussions.microsoft.com> wrote in message news:C03C95E4-7C9F-41AD-9734-5B918456F780@microsoft.com... > Hi, > > When I click on a link within an email I get an error message "the > operation > had been cancelled due to restrictions in effect on this computer." > > How can I change that s...

Hello 10-03-07
I'm new here can same 1 explain au it works "julio" <silva515@msn.com> a �crit dans le message de groupe de discussion : C90026A4-454B-46E2-82A9-A1F32B7AC3B5@microsoft.com... > I'm new here can same 1 explain au it works salut , qui peut me dire ce > que je dis faire pour faire partie de votre groupe " claudia " "julio" <silva515@msn.com> a �crit dans le message de groupe de discussion : C90026A4-454B-46E2-82A9-A1F32B7AC3B5@microsoft.com... > I'm new here can same 1 explain au it works hello!!!!!? I am new...

salut 01-14-08
slt ca va ...

closing an opportunity 10-06-06
Hello, I have a problem to close my own opportunity in crm 3.0. I get a errormassage "insufficient permissions". The role have all "user"-rights for opportunities. Wath other rights are necessary to close a opportunity? Thanks for your help! Give the permission to read Quote (least) or create Quote (Sales tab in the Security Roles). This is a usual problem and trust me, I don't understand what the quote have to do with this... but it works... it took me hours before I could find it. Way to go Microsoft!!!! Gerd Gottschall a =E9crit : > Hello, I have a problem...

GP 10 Security
How can we turn off the Group Reports access in certain modules? -- TC ...

Why is a scheduled 10 hour day overlap into the next day?
I have started a new project and have imported my WBS from excell, set my project start date, set my calender to a 10 hrs day seven days / week, outlined my phases, and am ready to schedule. The problem is that my 10 hour day is overlapping into the next day. I have had this problem in the past and cannot figure out why sometimes it happens and others it will behave like I expect. Can anyone shed some light on this? Could you address more specifically where you set your calendar? If you did it in Tools > Options > Calendar, then that is only partially the answer. You al...

Sending mail 02-01-10
I just set up a new acct. I can receive email fine just having problems sending email. I have changed my outgoing server and sent a message to the test address and it says its working. If I send to anyone besides the test address I get a message back from mail administator.saying mail system error in subject. Using AT&T Air Card? - -- bstanley To get help, you will need to provide more information than that. Note that it is unlikely the problem is a Windows Mail problem. You say "mail system error" but that is not enough. Can you copy and paste the relev...

Ver 10-Adding a new GL account message
Hi Folks Does anyone know how to permanently switch off the message that appears if you are adding a new GL account and you a defining a segment that has not been created in segment maintenance? I'd like to be able to switch off that prompt permanantly at a few clients as they do not want to make use of that functionality. This message also causes havoc when adding new account via Integration Manager. Many thanks Sheila The message already have "Don't Show" checkbox, right?! -- Regards, -- Mohammad R. Daoud MVP, MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - ...

Re: ��������������� 06-09-10
1) ����� ������� ������� ������ ��������� ������� ��� ����������� ��������, ������ ����� �������, ���� e-mail, ����� ������� ������ ��������. 2) � e-mail ������ ������������ � ������������, � � Usenet ����� ��������� ���� ����, �������� �� ������ ���� ��������. 3) ��������� ������� ���� � ������ ���� ����� � ����������� ����. 4) ��������� ������� ������ � ������ ����� ���������. 5) ��������� ������� ���� � ������ �����������. __________ Information from ESET NOD32 Antivirus, version of virus signature database 5183 (20100608) __________ The message was checked by ESET NOD32...