## sum products with ANDs

### sum products with ANDs

Hi, here is my formula:

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

### sum products with ANDs

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

### sum products with ANDs

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

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

### sum products with ANDs

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

### sum products with ANDs

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:

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

### sum products with ANDs

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

### sum products with ANDs

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

### sum products with ANDs

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.
such as when they signed up, and details about them, including a colum
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
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.

have the
this formula
with odd

in short, the latter

### sum products with ANDs

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
--
Don Guillett
SalesAid Software
XXXX@XXXXX.COM
"hannahmadsen >" << XXXX@XXXXX.COM > wrote in

### sum products with ANDs

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

### sum products with ANDs

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

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