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 entries in column A but this 
time where the names listed in column B are equal to more names listed in 
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to my 
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to nail 
it.
Any pointers gratefully received.
Cheers,
Steve.
0
3/5/2009 2:29:01 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
887 Views

Similar Articles

[PageSpeed] 33

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell 
references.

>=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

-- 
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
> 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 entries in column A but 
> this
> time where the names listed in column B are equal to more names listed in
> Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to 
> my
> SUMPRODUCT total (if the value in A7 is between 0 and 1).
>
> Keep skirting around the edges of this one without being quite able to 
> nail
> it.
> Any pointers gratefully received.
> Cheers,
> Steve. 


0
biffinpitt (3172)
3/5/2009 3:00:22 AM
Hi,
I get the gist of what you're saying but can't see how B3:B26 is being 
compared to C3:C26 for the three names?

            A                B                  C
3        0.12           Smith            Jones
4        2.03           Draper          Draper
5        0.65           Jones            Jones
6        0.81           Smith            Smith
7        0.33           Jones           Draper
8        1.52           Smith           Smith
9        0.74           Jones           Jones
10      3.02           Draper          Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name "Jones" 
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name "Smith" 
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name "Jones" 
(count 3)

Whilst names match on other rows or the value in column A is less than 1, 
only three times do all these criteria line up and this is what I need to 
count.
Cheers,
Steve.



"T. Valko" wrote:

> Use cells to hold the criteria** :
> 
> E3 = 0
> F3 = 1.01
> G3 = Jones
> H3 = Draper
> I3 = Smith
> 
> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
> 
> ** Use cells to hold the criteria:
> 
> You'd have to redo this formula since array constants can't use cell 
> references.
> 
> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
> 
> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
> wrote in message news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
> > 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 entries in column A but 
> > this
> > time where the names listed in column B are equal to more names listed in
> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to 
> > my
> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
> >
> > Keep skirting around the edges of this one without being quite able to 
> > nail
> > it.
> > Any pointers gratefully received.
> > Cheers,
> > Steve. 
> 
> 
> 
0
3/5/2009 4:00:00 AM
I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

-- 
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:7294072D-82C0-4922-BBE7-A18BC7A651B3@microsoft.com...
> Hi,
> I get the gist of what you're saying but can't see how B3:B26 is being
> compared to C3:C26 for the three names?
>
>            A                B                  C
> 3        0.12           Smith            Jones
> 4        2.03           Draper          Draper
> 5        0.65           Jones            Jones
> 6        0.81           Smith            Smith
> 7        0.33           Jones           Draper
> 8        1.52           Smith           Smith
> 9        0.74           Jones           Jones
> 10      3.02           Draper          Jones
>
> For the table illustrated I need my formula to return the value of "3".
>
> A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name 
> "Jones"
> (count 1)
> A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name 
> "Smith"
> (count 2)
> A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name 
> "Jones"
> (count 3)
>
> Whilst names match on other rows or the value in column A is less than 1,
> only three times do all these criteria line up and this is what I need to
> count.
> Cheers,
> Steve.
>
>
>
> "T. Valko" wrote:
>
>> Use cells to hold the criteria** :
>>
>> E3 = 0
>> F3 = 1.01
>> G3 = Jones
>> H3 = Draper
>> I3 = Smith
>>
>> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
>>
>> ** Use cells to hold the criteria:
>>
>> You'd have to redo this formula since array constants can't use cell
>> references.
>>
>> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>>
>> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Struggling in Sheffield" 
>> <StrugglinginSheffield@discussions.microsoft.com>
>> wrote in message 
>> news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
>> > 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 entries in column A but
>> > this
>> > time where the names listed in column B are equal to more names listed 
>> > in
>> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added 
>> > to
>> > my
>> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
>> >
>> > Keep skirting around the edges of this one without being quite able to
>> > nail
>> > it.
>> > Any pointers gratefully received.
>> > Cheers,
>> > Steve.
>>
>>
>> 


0
biffinpitt (3172)
3/5/2009 4:41:58 AM
Hi Biff,
Works a treat, many thanks.

"T. Valko" wrote:

> I misunderstood your requirement.
> 
> I thought you just wanted to match Jones and Jones.
> 
> Try this:
> 
> E3 = 0
> F3 = 1.01
> G3 = Jones
> H3 = Draper
> I3 = Smith
> 
> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
> wrote in message news:7294072D-82C0-4922-BBE7-A18BC7A651B3@microsoft.com...
> > Hi,
> > I get the gist of what you're saying but can't see how B3:B26 is being
> > compared to C3:C26 for the three names?
> >
> >            A                B                  C
> > 3        0.12           Smith            Jones
> > 4        2.03           Draper          Draper
> > 5        0.65           Jones            Jones
> > 6        0.81           Smith            Smith
> > 7        0.33           Jones           Draper
> > 8        1.52           Smith           Smith
> > 9        0.74           Jones           Jones
> > 10      3.02           Draper          Jones
> >
> > For the table illustrated I need my formula to return the value of "3".
> >
> > A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name 
> > "Jones"
> > (count 1)
> > A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name 
> > "Smith"
> > (count 2)
> > A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name 
> > "Jones"
> > (count 3)
> >
> > Whilst names match on other rows or the value in column A is less than 1,
> > only three times do all these criteria line up and this is what I need to
> > count.
> > Cheers,
> > Steve.
> >
> >
> >
> > "T. Valko" wrote:
> >
> >> Use cells to hold the criteria** :
> >>
> >> E3 = 0
> >> F3 = 1.01
> >> G3 = Jones
> >> H3 = Draper
> >> I3 = Smith
> >>
> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
> >>
> >> ** Use cells to hold the criteria:
> >>
> >> You'd have to redo this formula since array constants can't use cell
> >> references.
> >>
> >> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
> >>
> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Struggling in Sheffield" 
> >> <StrugglinginSheffield@discussions.microsoft.com>
> >> wrote in message 
> >> news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
> >> > 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 entries in column A but
> >> > this
> >> > time where the names listed in column B are equal to more names listed 
> >> > in
> >> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added 
> >> > to
> >> > my
> >> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
> >> >
> >> > Keep skirting around the edges of this one without being quite able to
> >> > nail
> >> > it.
> >> > Any pointers gratefully received.
> >> > Cheers,
> >> > Steve.
> >>
> >>
> >> 
> 
> 
> 
0
3/5/2009 12:28:12 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:997BBC0C-FAC1-4022-87B8-B532CD7B2490@microsoft.com...
> Hi Biff,
> Works a treat, many thanks.
>
> "T. Valko" wrote:
>
>> I misunderstood your requirement.
>>
>> I thought you just wanted to match Jones and Jones.
>>
>> Try this:
>>
>> E3 = 0
>> F3 = 1.01
>> G3 = Jones
>> H3 = Draper
>> I3 = Smith
>>
>> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Struggling in Sheffield" 
>> <StrugglinginSheffield@discussions.microsoft.com>
>> wrote in message 
>> news:7294072D-82C0-4922-BBE7-A18BC7A651B3@microsoft.com...
>> > Hi,
>> > I get the gist of what you're saying but can't see how B3:B26 is being
>> > compared to C3:C26 for the three names?
>> >
>> >            A                B                  C
>> > 3        0.12           Smith            Jones
>> > 4        2.03           Draper          Draper
>> > 5        0.65           Jones            Jones
>> > 6        0.81           Smith            Smith
>> > 7        0.33           Jones           Draper
>> > 8        1.52           Smith           Smith
>> > 9        0.74           Jones           Jones
>> > 10      3.02           Draper          Jones
>> >
>> > For the table illustrated I need my formula to return the value of "3".
>> >
>> > A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name
>> > "Jones"
>> > (count 1)
>> > A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name
>> > "Smith"
>> > (count 2)
>> > A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name
>> > "Jones"
>> > (count 3)
>> >
>> > Whilst names match on other rows or the value in column A is less than 
>> > 1,
>> > only three times do all these criteria line up and this is what I need 
>> > to
>> > count.
>> > Cheers,
>> > Steve.
>> >
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Use cells to hold the criteria** :
>> >>
>> >> E3 = 0
>> >> F3 = 1.01
>> >> G3 = Jones
>> >> H3 = Draper
>> >> I3 = Smith
>> >>
>> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
>> >>
>> >> ** Use cells to hold the criteria:
>> >>
>> >> You'd have to redo this formula since array constants can't use cell
>> >> references.
>> >>
>> >> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>> >>
>> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Struggling in Sheffield"
>> >> <StrugglinginSheffield@discussions.microsoft.com>
>> >> wrote in message
>> >> news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
>> >> > 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 entries in column A 
>> >> > but
>> >> > this
>> >> > time where the names listed in column B are equal to more names 
>> >> > listed
>> >> > in
>> >> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the 
>> >> > added
>> >> > to
>> >> > my
>> >> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
>> >> >
>> >> > Keep skirting around the edges of this one without being quite able 
>> >> > to
>> >> > nail
>> >> > it.
>> >> > Any pointers gratefully received.
>> >> > Cheers,
>> >> > Steve.
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
3/5/2009 2:36:32 PM
Reply:

Similar Artilces:

autopopulate sl.no.based on a cell value
I have cell value in work sheet based on a function and it changes accordingly.Based on this cell value I want to populate a serial numbers in a column.suppose c1=15,Iwant to populate from d10:d100 serial number as 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 upto d24 only rest should appear as blank.Iwant this funtion as array that means d10:d100= formula.maximum cell value is around 90.Any idea to get this result. In D10 enter: =IF(ROWS($1:1)<=$C$1,ROWS($1:1),"") and copy down to D100 Hope this helps Rowan TUNGANA KURMA RAJU wrote: > I have cell value in work sheet based on a ...

new pc #2
hi. I need to get a new hard drive. I've backed everything up from My Documents. For Outlook (2003) I've searched for *.pst . Thing is, if there are a few pst files for example, you need to check the date to assess which one is the one you are using. Is there not a better way of doing this? Say from Outlook itself, to see where its source file is stored. Thanks J Sure. File > Open Outlook Data File... -- Russ Valentine [MVP-Outlook] "ZIp" <noone@syawhere.com> wrote in message news:%237DiRoE%23FHA.160@TK2MSFTNGP12.phx.gbl... > hi. I need to get a new hard ...

Excel Rows and Columns Issue
I am an excel 2002 user, and I am having problem putting in th formulas. The formulas are simple, average, add, subtract, an multiplying cells, but the real problem is in "viewing" the rows an columns. Normally, in excel, rows are numbered (1,2,3,4...) and columns ar alphabatized (A,B,C,D...) In my case, both columns and rows ar "numbered" , so putting in a formula is a pain in my neck. How d resolve or change the view of the excel sheet so it displays th "Letters" on the top columns part of the sheet going from left t right, and the "numbers" rows (wh...

Fonts #2
Help!! How can I make my font size larger than 72? Also, I have downloaded many fonts to files but how do I use them in a Document? Desparate for this info!! Thank you Select cell(s), then the font size (in the window). While it is selected, type in the size you want. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Valerie" <Rosa2mob@aol.com> wrote in message news:03f301c394e4$fde678a0$a101280a@phx.gbl... > Help!! How can I make my font size larger than 72? > Also, I have downloaded many fonts to files but how do I > use them in a Document? &g...

Column Headers/headings
Hi, Is there any way that you can get the top row to stay visible no matter where you are on the spread sheet - ie: to be able to see what columns you are typing in (eg cost) when further down the spread sheet. Thanks for any help, Matt. Click on A2 Click Window > Freeze Panes -- Rgds Max xl 97 ---------------------------------------------------- Use xdemechanik <at>yahoo<dot>com for email ----------------------------------------------------- "The Stoat" <thefantstoat@lycos.co.uk> wrote in message news:5iSNb.3831$pD3.3270@news-binary.blueyonder.co.uk... &...

What function would I use? #2
Thanks for the suggestion it did work really well, but I think I'm dealing with a little too much data for it. This is an example of what I'm trying to do. Product 1 -------------------------------------------------------- Customer name | accesorie a |accesorie b| accessorie c | -------------------------------------------------------- Bob | yes | | | -------------------------------------------------------- Mary | | yes | | ------------------------------------------...

Calendar #2
Hi All, I'm trying to put together a workbook which will allow me to enter a start date and finish date (different cells) in Sheet 1. Is there a way of transfering this to a "Calendar" (essentially a row of numbers) automatically on Sheet 2 and identify, perhaps by colour? I would visualise it similar to below: On Sheet 1 Start Finish Holiday 01/01/2007 05/01/2007 On Sheet 2 January 2007 S M T W T F S 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Thanks in...

Total cells of one column based on the values in another column?
One of the sheets in my ledger workbook is for annual cash receipts. Column D is for the amount (formatted to number-accounting)and Column E is for who it's from (formatted to number-text). I've got my cells elsewhere for month, quarter, & year totals. The problem is I have to manually, with my adding machine, go thru and add up the total from each source. (How much total income for the year is/was from Co.A, Co.B, Co.C, etc..) I've figured out how to use conditional formatting so that if the text in Column E cells is say, Co.A, the text will be red, if Co.B, text will ...

Column Labels on Views
Hi, does anybody know if I can change a the Display Name of an existing column field? I checked Deployment manager and noticed the Display Name is prompted during creation of the field, is there a way to change the label? we reused a field for the accounts table and would like to change the display name on the List View.. Thanks =) Simple log in the CRM web page with administrator level and go to Start/Configuration/Custom System, choose the entity who you want custom and click in custom form. In this page you can choose the displayed fields and custom them. After custom the disired fie...

Holding the view of column one
Good morning, I want to hold the view of column one while working on other columns. What is the best approach to my need? Thank you, Mark Windows>Freeze panes -- Kind Regards, Niek Otten Microsoft MVP - Excel "Mark S." <Mark S.@discussions.microsoft.com> wrote in message news:0DDD2DBD-1BF5-4D11-AA96-86368E7BA8E6@microsoft.com... > Good morning, > > I want to hold the view of column one while working on other columns. What > is the best approach to my need? > > Thank you, Mark ...

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Thick column lines
Hello I want to create thick column lines but don't know how. Thick lines would enable me to separate the data from one column to next more clearly. Please help. Thank you. Regards hash Format / Cells / Borders / Select thick border and click on diagram to tell Excel where to apply it on the cell(s). -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- There's no 'I' in 'Team' ---------...

Ordering Columns in a Cross Tab Query & Dividing a Value
Hello, First question, is there a way to specify what order the columns appear in a cross tab query? Second, is there a way to distribute a value in one column of a cross tab query across new columns? For example, if I have a total value of a contract, but I needed to recognize revenue equally in each of the three months of a quarter, is there a way to distribute that value across columns in a cross tab? Thanks! >> First question, is there a way to specify what order the columns appear in a cross tab query? In the SQL view edit the PIVOT line to something like this (Make sure...

MATCHING COLUMNS
I HAVE A QUESTION ABOUT MATCHING COLUMNS IN EXCEL. FOR INSTANCE I HAVE TWO COLUMNS ONE IN A AND ONE IN B WITH ALL DIFFERENT VALUES. HOW DO I MATCH COLUMN A WITH COLUMN B? AN EXAMPLE IS BELOW A B 000824108498310 000824108378310 000825252525151 000824108378310 002020204582810 000824108448310 020202222225550 000824108460310 None of your values matches any value from other column! Regards, -- AP PS: ALL CAPS means yelling: please avoid! <HOOSICK@NYCAP.RR.COM> a...

Creating emailing list from column of addresses
I am using Excel 2003 In each cell of a column I have a unique email address - several hundred in total. I want to send the same circular email (using Outlook 2003) to each address in the column Is it straightforward to convert the list into a single string separated by semicolons that can be cut and pasted into the BCC field of an email? Is there a better way? Thanks all Howard Is there a need to make XL do all that work? When I copy the cells, go to Outlook, and hit paste, it handles them just fine. -- Best Regards, Luke M "Howard" <man_o_law@hotma...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

Difference between 2 Opportunities reports
Can you help me to understand the difference between 2 Opportunity reports: 1. Pipeline Chart report by sales stages 2. Pipeline Chart report forecast by sales stages ...

Automatic Line Numbers in a Column?
Would like to put a line number in my Column A of a spreadsheet. And if possible have it renumber itself should I sort the rows. Can this be done, without any major macro programming? Al, not sure this is what you want but try =ROW() -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Al Franz" <albert@nospam.netmation.com> wrote in message news:OOnTyxrIFHA.2648@TK2MSFTNGP14.phx.gbl... > Would like to put a line...

Highlighting the selected row or column
Is there a way to put the cursor in a cell and have that entire row and/or column highlighted? I would like the highlighting to be temporary and move with the selected cell. Thank you in advance. Lewis, have a look at Chip's addin at http://www.cpearson.com/excel/rowliner.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lewis Clark" <lewis_clark_644 @ yahoo.com> wrote in message news:HwLce.637$nN.574@trn...

Print Column Headings on multiple sheets
I have a budget that when printed is one page wide and three tall. How can I add the column headings to the second and third pages? click file/page setup and click the sheet tab click the icon next to rows to repeat at top select the rows click the icon again and then ok -- Gary Keramidas Excel 2003 "wx4usa" <wx4usa@gmail.com> wrote in message news:19421c6b-0ea5-4524-8574-8dea96d44bde@22g2000yqr.googlegroups.com... >I have a budget that when printed is one page wide and three tall. How > can I add the column headings to the second and third pages?...

Clustered and stacked columns at the same time
Hi, I have a series of data that I would like to be represented on a single chart in clustered and stacked columns. I will explain in details the situation. I have sales data for the 4 regions, viz. North, East, West South. The data is additionally categorized across hardware sales and software sales. These data are grouped per month. Thus, I have a matrix as follows for each region: Period Hardware Sales Software Sales Jan xxx yyy Feb aaa bbb Mar ppp qqq .... .... What I wanted was to have a chart where the x asix is...

Mapping an XML element more than once?? #2
I'm sorry, I don't think I've made myself clear... I've mapped my XML schema to XML Source... and I'm trying to map a child element to multiple cells.... in the Microsoft troubleshooting section, they say "Multiple repeating elements are mapped to the same XML list, and the repetition not defined by an ancestor element" - but I don't know how to fix that. Please... anyone? -- AStarWithin ...

reporting tools #2
What reporting tools do you all like? I have looked at a couple but would like to know what you all recommend? I'd recommend Quest MessageStats. http://www.quest.com/messagestats/ Nue "Brandon" <whocares@you.com> wrote in message news:%23Y%231IlbeGHA.1436@TK2MSFTNGP05.phx.gbl... > What reporting tools do you all like? I have looked at a couple but would > like to know what you all recommend? > > ...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...