Hi, here is my formula:

=SUMPRODUCT(--('All sign ups'!$C$1:$C$3000=$A7),--('All sig

ups'!$J$1:$J$3000=1))

but i want the product to sum values in column 'all sign ups C' tha

don't just have the same name as A7, but have the same name as A7, B

and C7.

(basically the b and c columns are aliases or AKAs for column A names)

So, based on the formula above, i want:

to count 'the number of times a cell that appears the same as A7, B

AND C7' in column 'all sign ups C', where column J as the value of 1.

Thanks!

Hanna

How about:-

=SUMPRODUCT(--('All sign ups'!$C$1:$C$3000=$A7),--('All sign

ups'!$C$1:$C$3000=$B7),--('All sign ups'!$C$1:$C$3000=$C7),--('All sign

ups'!$J$1:$J$3000=1))

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ( http://www.yqcomputer.com/ ).

Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004

=SUMPRODUCT(--('All sign ups'!$C$1:$C$3000=$A7),--('All sign

ups'!$C$1:$C$3000=$B7),--('All sign ups'!$C$1:$C$3000=$C7),--('All sign

ups'!$J$1:$J$3000=1))

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ( http://www.yqcomputer.com/ ).

Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004

Forget that - Didn't read the question properly, though I'm a tad confused. Do

you mean you want entries that equal A7 OR B7 OR C7, in which case try

=SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A7)+('All sign

ups'!$C$1:$C$3000=$A7)+('All sign ups'!$C$1:$C$3000=$A7)),--('All sign

ups'!$J$1:$J$3000=1))

I may still have misunderstood you though.

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ( http://www.yqcomputer.com/ ).

Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004

you mean you want entries that equal A7 OR B7 OR C7, in which case try

=SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A7)+('All sign

ups'!$C$1:$C$3000=$A7)+('All sign ups'!$C$1:$C$3000=$A7)),--('All sign

ups'!$J$1:$J$3000=1))

I may still have misunderstood you though.

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ( http://www.yqcomputer.com/ ).

Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004

sorry, i realised it is a bit confusing, i want to sum it if the name i

A7, B7 OR C7, but i want the sum of all of those names, not just of on

of them or the sum of the other or the sum of the other if you see wha

i mean.

i don't want the sum of just one of the aliases, but i want the su

count of all the names, AND their aliases..

A7, B7 OR C7, but i want the sum of all of those names, not just of on

of them or the sum of the other or the sum of the other if you see wha

i mean.

i don't want the sum of just one of the aliases, but i want the su

count of all the names, AND their aliases..

hi,

it also seems to count blanks (ie if C7 is blank, because A7 only ha

one alias, listed in B7, then the formula counts all the blank sig

ups!)

is there anyway i can get it not to count blanks, other than to put n/

in all the names that don't have extra aliases?

For the moment i've put:

=SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A8)+('All sig

ups'!$C$1:$C$3000=$B8)+('All sign ups'!$C$1:$C$3000=$C8)),--(('All sig

ups'!$J$1:$J$3000=1)+('All sign ups'!$J$1:$J$3000=2)))

as column J currently only has either a 1 or 2 in it, and only i

column C has a value (ie not blank). But this isn't ideal, as column

may change.

thanks!

hanna

it also seems to count blanks (ie if C7 is blank, because A7 only ha

one alias, listed in B7, then the formula counts all the blank sig

ups!)

is there anyway i can get it not to count blanks, other than to put n/

in all the names that don't have extra aliases?

For the moment i've put:

=SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A8)+('All sig

ups'!$C$1:$C$3000=$B8)+('All sign ups'!$C$1:$C$3000=$C8)),--(('All sig

ups'!$J$1:$J$3000=1)+('All sign ups'!$J$1:$J$3000=2)))

as column J currently only has either a 1 or 2 in it, and only i

column C has a value (ie not blank). But this isn't ideal, as column

may change.

thanks!

hanna

Here is a sumproduct formula that will sum all with either a,b,or c

=SUMPRODUCT((K19:K22={"a","B","C"})*L19:L22)

More complicated if you want to refer to a range. Here is an example I did

recently.

Sub AverageIFmulti()

For Each c In Range("c11:c12") 'Selection

ms = ms & "," & """" & c & """"

Next

ms = "{" & Right(ms, Len(ms) - 1) & "}"

MsgBox ms

Range("c1").Formula = _

"=sumproduct((h3:h23=" & ms & ")*k3:k23)/sumproduct((h3:h23=" & ms & ")*1)"

End Sub

--

Don Guillett

SalesAid Software

XXXX@XXXXX.COM

"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

=SUMPRODUCT((K19:K22={"a","B","C"})*L19:L22)

More complicated if you want to refer to a range. Here is an example I did

recently.

Sub AverageIFmulti()

For Each c In Range("c11:c12") 'Selection

ms = ms & "," & """" & c & """"

Next

ms = "{" & Right(ms, Len(ms) - 1) & "}"

MsgBox ms

Range("c1").Formula = _

"=sumproduct((h3:h23=" & ms & ")*k3:k23)/sumproduct((h3:h23=" & ms & ")*1)"

End Sub

--

Don Guillett

SalesAid Software

XXXX@XXXXX.COM

"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

I'm definitely confused now :-) Are A8, B8, C8 the ONLY cells that you have the

alias' in, or does this carry on down the sheet and you are copying this formula

down? eg you have Id and alias' in A9, B9, C9, then A10, B10, C10 etc with odd

blanks in this data

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ( http://www.yqcomputer.com/ ).

Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004

alias' in, or does this carry on down the sheet and you are copying this formula

down? eg you have Id and alias' in A9, B9, C9, then A10, B10, C10 etc with odd

blanks in this data

--

Regards

Ken....................... Microsoft MVP - Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------

It's easier to beg forgiveness than ask permission :-)

----------------------------------------------------------------------------

---

Outgoing mail is certified Virus Free.

Checked by AVG anti-virus system ( http://www.yqcomputer.com/ ).

Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004

Ok, in column A i have a list of names, in column B and C i have thei

corresponding aliases, some have 1 (ie just in column B), some have

(b and c) and some have none. So, whilst column A is always filled,

and C isn't always, just for some of the rows.

In worksheet 'all sign ups' i have thousands of sign ups, with detail

such as when they signed up, and details about them, including a colum

('all sign ups'!C:C) that has a field they chose from a drop down menu

which is a choice names and aliases. Each entry in sign ups has ON

name, that is represented in on of the rows in one of the columns A,

or C.

In columns (all sign ups!J:J) i have the year they signed up (1, 2,

etc).

I want to do a count of the number of people that signed up from eac

name, (in my column A) including the number that signed up to th

aliases.

So in my worksheet, i will have a column next to the name that count

all sign ups, a column for sign ups in year 1, a column for sign ups i

year 2 etc.

So this is why i want to count, the total number of signups includin

the aliases, given the year is...

But, because i can't do A:A in sum product, i have to do A1:A3000, a

the signups is constantly being added to. As a result, the sum produc

is calculating over a lot of blanks, and counts the blanks as signup

in the total sign ups column for those names that dont' have 2 aliases

I have currently got around this by specifying that the year (all sig

ups j1:j3000) must be 1 or 2, though obviously the years will increase

and i don't want to have to keep adding a year each time.

=SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A8)+('All sig

ups'!$C$1:$C$3000=$B8)+('All sign ups'!$C$1:$C$3000=$C8)),--(('All sig

ups'!$J$1:$J$3000=1)+('All sign ups'!$J$1:$J$3000=2)))

have the

this formula

with odd

in short, the latter

corresponding aliases, some have 1 (ie just in column B), some have

(b and c) and some have none. So, whilst column A is always filled,

and C isn't always, just for some of the rows.

In worksheet 'all sign ups' i have thousands of sign ups, with detail

such as when they signed up, and details about them, including a colum

('all sign ups'!C:C) that has a field they chose from a drop down menu

which is a choice names and aliases. Each entry in sign ups has ON

name, that is represented in on of the rows in one of the columns A,

or C.

In columns (all sign ups!J:J) i have the year they signed up (1, 2,

etc).

I want to do a count of the number of people that signed up from eac

name, (in my column A) including the number that signed up to th

aliases.

So in my worksheet, i will have a column next to the name that count

all sign ups, a column for sign ups in year 1, a column for sign ups i

year 2 etc.

So this is why i want to count, the total number of signups includin

the aliases, given the year is...

But, because i can't do A:A in sum product, i have to do A1:A3000, a

the signups is constantly being added to. As a result, the sum produc

is calculating over a lot of blanks, and counts the blanks as signup

in the total sign ups column for those names that dont' have 2 aliases

I have currently got around this by specifying that the year (all sig

ups j1:j3000) must be 1 or 2, though obviously the years will increase

and i don't want to have to keep adding a year each time.

=SUMPRODUCT(--(('All sign ups'!$C$1:$C$3000=$A8)+('All sig

ups'!$C$1:$C$3000=$B8)+('All sign ups'!$C$1:$C$3000=$C8)),--(('All sig

ups'!$J$1:$J$3000=1)+('All sign ups'!$J$1:$J$3000=2)))

have the

this formula

with odd

in short, the latter

For the changing range, why not define them? Insert>name>define>

name=ColC

refersto=offset($c$1,0,0,counta($c:$c),1)

name colG

refersto=offset($g$1,0,0,counta($c:$c),1)

Notice I still count Column C instead of G

then use colc and colg in your formula instead of 1-3000

--

Don Guillett

SalesAid Software

XXXX@XXXXX.COM

"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

name=ColC

refersto=offset($c$1,0,0,counta($c:$c),1)

name colG

refersto=offset($g$1,0,0,counta($c:$c),1)

Notice I still count Column C instead of G

then use colc and colg in your formula instead of 1-3000

--

Don Guillett

SalesAid Software

XXXX@XXXXX.COM

"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

oooh - i've never used define - how would i put them in the equation? d

they need quote marks - can you write the equation for me?

thanks

they need quote marks - can you write the equation for me?

thanks

just substitute the defined name for the range

a1:x200

myrangename

--

Don Guillett

SalesAid Software

XXXX@XXXXX.COM

"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

a1:x200

myrangename

--

Don Guillett

SalesAid Software

XXXX@XXXXX.COM

"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

1. Calculated field question sum of a product rather then the product of sums

3. separating product terms from sum-of-products (symbolic)

4. How to perform sum sum sum...

5. How to Sum<=40, sum, if >40 sum in next column

6. Sum sum sum

7. "Sum" according to products

8. Ranking With Sum Product Formulas over Autofiltered Cells

11. Need help summing the total of two columns with their products

12. Sum the products of a formulas

14. Sum by selecting each product

11 post • Page:**1** of **1**