Tricky LONG formula, need a shorter version, there has to be a way

[Guest]

Ok. I have a formula that is too long to put in one cell. So it has been
spread down 10 or so cell's then used =SUM(N3:N300) to add them together
(right down the bottom of the spreadsheet hidden away). Im sure there is a
way to get this smaller by using a relative range rather than a different
formula for each row (and not have huge tables at the bottom doing all the
working, just keep it to the one cell)... I need it to read 2 separate
columns data only horizontally across the rows, but over a range of 300 rows.

Basically i need to be able to know if a person has done a certain thing, in
a certain place. Column N-X are the people, and column AC-AF are sales in
certain cities.The columns N-X with 1's in them are appointments for the
person (eg N=nik O=emma etc.). Then in column AC (eg AC=Auckland AD=Hamilton
etc) if there is a 1 they have made a sale from that appointment, if nothing,
then no sale. So, came up with this.. =IF(N3+AC3=2,1,0) described as.. (If
column N (Nik) + column AC (Auckland) = 2, add 1 to this cell (the TOTAL
which means Nik made a sale in Auckland), if it doesnt equal 2 then add
nothing, no sale...

Heres the hard part. That equation only covers N3 (nik, row 3) and AC3 (row
3, making a sale in Auckland). But i also need it to recognise Hamilton, and
2 other cities. Where i made another table, replacing AC with AD for each..
But the amount of formula/equations is massively unecessary.. As it looks
like this...

=IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1,0)+IF(N6+AC6=2,1,0)+IF(N7+AC7=2,1,0)
+IF(N8+AC8=2,1,0)+IF(N9+AC9=2,1,0)+IF(N10+AC10=2,1,0)+IF(N11+AC11=2,1,0)+IF(N12+AC12=2,1,0)
+IF(N13+AC13=2,1,0)+IF(N14+AC14=2,1,0)+IF(N15+AC15=2,1,0)+IF(N16+AC16=2,1,0)+IF(N17+AC17=2,1,0)
+IF(N18+AC18=2,1,0)+IF(N19+AC19=2,1,0)+IF(N20+AC20=2,1,0)+IF(N21+AC21=2,1,0)+IF(N22+AC22=2,1,0)
+IF(N23+AC23=2,1,0)+IF(N24+AC24=2,1,0)+IF(N25+AC25=2,1,0)+IF(N26+AC26=2,1,0)+IF(N27+AC27=2,1,0)
+IF(N28+AC28=2,1,0)+IF(N29+AC29=2,1,0)+IF(N30+AC30=2,1,0)+IF(N31+AC31=2,1,0)+IF(N32+AC32=2,1,0)
+IF(N33+AC33=2,1,0)+IF(N34+AC34=2,1,0)+IF(N35+AC35=2,1,0)+IF(N36+AC36=2,1,0)+IF(N37+AC37=2,1,0)
+IF(N38+AC38=2,1,0)+IF(N39+AC39=2,1,0)+IF(N40+AC40=2,1,0)+IF(N41+AC41=2,1,0)+IF(N42+AC42=2,1,0)
+IF(N43+AC43=2,1,0)+IF(N44+AC44=2,1,0)+IF(N45+AC45=2,1,0)+IF(N46+AC46=2,1,0)+IF(N47+AC47=2,1,0)
+IF(N48+AC48=2,1,0)+IF(N49+AC49=2,1,0)+IF(N50+AC50=2,1,0)+IF(N51+AC51=2,1,0)+IF(N52+AC52=2,1,0)
+IF(N53+AC53=2,1,0)+IF(N54+AC54=2,1,0)+IF(N55+AC55=2,1,0)+IF(N56+AC56=2,1,0)+IF(N57+AC57=2,1,0)
+IF(N58+AC58=2,1,0)+IF(N59+AC59=2,1,0)+IF(N60+AC60=2,1,0)+IF(N61+AC61=2,1,0)+IF(N62+AC62=2,1,0)
+IF(N63+AC63=2,1,0)+IF(N64+AC64=2,1,0)+IF(N65+AC65=2,1,0)+IF(N66+AC66=2,1,0)+IF(N67+AC67=2,1,0)
+IF(N68+AC68=2,1,0)+IF(N69+AC69=2,1,0)+IF(N70+AC70=2,1,0)+IF(N71+AC71=2,1,0)+IF(N72+AC72=2,1,0)
+IF(N73+AC73=2,1,0)+IF(N74+AC74=2,1,0)+IF(N75+AC75=2,1,0)+IF(N76+AC76=2,1,0)+IF(N77+AC77=2,1,0)
+IF(N78+AC78=2,1,0)+IF(N79+AC79=2,1,0)+IF(N80+AC80=2,1,0)+IF(N81+AC81=2,1,0)+IF(N82+AC82=2,1,0)
+IF(N83+AC83=2,1,0)+IF(N84+AC84=2,1,0)+IF(N85+AC85=2,1,0)+IF(N86+AC86=2,1,0)+IF(N87+AC87=2,1,0)
+IF(N88+AC88=2,1,0)+IF(N89+AC89=2,1,0)+IF(N90+AC90=2,1,0)+IF(N91+AC91=2,1,0)+IF(N92+AC92=2,1,0)
+IF(N93+AC93=2,1,0)+IF(N94+AC94=2,1,0)+IF(N95+AC95=2,1,0)+IF(N96+AC96=2,1,0)+IF(N97+AC97=2,1,0)
+IF(N98+AC98=2,1,0)+IF(N99+AC99=2,1,0)+IF(N100+AC100=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102+AC102=2,1,0)

And that only covers Nik - Auckland, up to row 102. And is spread out over
many cells, then summed as described earlier. Is there a way i can range
this? Id want it to be something like =IF(N3:N300+AC3:AC300=2,1,0) so it does
the same thing but covers all the rows, in one cell.. But that formula doesnt
work...

Any ideas? Im using Excel 2002. Yes, old school.

Any help appriciated.

Cheers.

Nik.

 

[Harlan Grove]

Basically i need to be able to know if a person has done a certain
thing, in a certain place. Column N-X are the people, and column
AC-AF are sales in certain cities. The columns N-X with 1's in them
are appointments for the person (eg N=nik O=emma etc.). Then in
column AC (eg AC=Auckland AD=Hamilton etc) if there is a 1 they have
made a sale from that appointment, if nothing, then no sale. . . . ....
Heres the hard part. That equation only covers N3 (nik, row 3) and
AC3 (row 3, making a sale in Auckland). But i also need it to
recognise Hamilton, and 2 other cities. Where i made another table,
replacing AC with AD for each. But the amount of formula/equations is
massively unecessary.. As it looks like this...

=IF(N3+AC3=2,1,0)+IF(N4+AC4=2,1,0)+IF(N5+AC5=2,1,0) ....
IF(N100+AC100=2,1,0)+IF(N101+AC101=2,1,0)+IF(N102+AC102=2,1,0)

....

For all 4 cities at once, use

=SUMPRODUCT(--(N3:N102+AC3:AF102=2))

 

[Guest]

A quick and dirty way to do it:

=SUMPRODUCT( --( N$3:N$300 + $AC$3:$AF$300 = 2 ) )

But if your data contains non numeric values, it will fail...

 

[Pete_UK]

Strange way of arranging your data! You must have forgotten that zero
times anything is zero and 1 times 1 is 1, so if you multiply the
cells together you will get either 1 or 0, thus dispensing with the
need for IF. You then need to add the 1's and 0's together, and this
is what SUMPRODUCT does. If your data stretches from rows 3 to 300,
try this formula to add up the successes for Nik (column N) in Aukland
(column AC):

=SUMPRODUCT((N3:N300)*(AC3:AC300))

This will replace your monstrous formula quoted below. Hopefully you
can see how to adapt it for other people and for other places.

Hope this helps.

Pete

 

[Guest]

Im going to give it a go, but im not trying to multiply, im basically using 1
as a marker, so at the bottom it works out how many appointments compared to
sales. 1 in column N + 1 in column AC = 1 sale in the total cell box. But if
there is a 1 in N and nothing in AC then nothing gets added.

My formula is working perfectly. There must be a way to write exactlly the
same thing to make it smaller tho, thats what im asking..

Il try all of these and get back to you's.

Thanks.

 

[Guest]

Thanks, works perfect. I just changed the range you gave back to individual
cities as i need to know exactly where the sale was made in the total. And
the sales people move form city to city.

But works a charm.

Mint!

Nik

 

[Guest]

Oh, is there any chance you could give me a breif breakdown of what that
command consists of?

This is the first time ive used spreadsheets really, so i thought i was
doing well to have worked out my original formula.. lol.

Cheers.

 

[Pete_UK]

Follow this link for an excellent explanation about SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

Pete