Developer Forum

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

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

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..

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

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

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

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

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

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

just substitute the defined name for the range
a1:x200
myrangename

--
Don Guillett
SalesAid Software
XXXX@XXXXX.COM
"hannahmadsen >" << XXXX@XXXXX.COM > wrote in