sum products with ANDs

[hannahmadsen]

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

 

[Ken Wright]

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

 

[Ken Wright]

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

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

 

[hannahmadsen]

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

 

[hannahmadsen]

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

 

[Don Guillett]

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

 

[Ken Wright]

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

 

[hannahmadsen]

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

I'm definitely confused now Are A8, B8, C8 the ONLY cells that yo have the
alias' in, or does this carry on down the sheet and you are copyin this formula
down? eg you have Id and alias' in A9, B9, C9, then A10, B10, C10 et with odd
blanks in this data

in short, the latter

 

[Don Guillett]

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

 

[hannahmadsen]

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

 

[Don Guillett]

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