Thanks for all the help everyone, I got it from just playing around with
Pete's advise,

Your all the best and could never do this wonderful thing called excel


"phuser" wrote in message
news:suadne8KotyoeYTVnZ2dnUVZ_uGdnZ2d@sasktel...
this returns a #value

on the right track though?

=SUMPRODUCT(COACCT2008!$B$2:$B$3000=C28)*(SUMIF(CO ACCT2008!$A$2:$A$3000,A28,COACCT2008!C2:C3000)+(CO ACCT2008!$A$2:$A$3000,B28,COACCT2008!$C$2:$C$3000) )

"Pete_UK" wrote in message
...
Can the cells in column A be equal to A21 AND B21 at the same time?
(well, only if A21=B21).

Do you mean equal to A21 OR equal to B21? If so, try this variation:

=SUMPRODUCT(((COACCT2008!$A$2:$A$3000=A21)+(COACCT 2008!$A$2:$A
$3000=B21))*(CO*ACCT2008!$B$2:$B$3000=C21),COACCT2 008!$C$2:$C$3000)

Here the + is equivalent to OR.

Hope this helps.

Pete

On Apr 30, 11:41 pm, "phuser" wrote:
Just when you think you got it all figured out, you need something else
*sigh*

Now Im trying to filter by 2 criteria in COACCT2008 ColumnA, this returns
a
0.00 and I'm starting to think that the SUMPRODUCT does not work if you
are
using the same column.

=SUMPRODUCT((COACCT2008!$A$2:$A$3000=A21)*(COACCT2 008!$A$2:$A$3000=B21)*(CO*ACCT2008!$B$2:$B$3000=C2 1),COACCT2008!$C$2:$C$3000)

Any other ideas?

"phuser" wrote in message

news:t5adnaixDqvAPoXVnZ2dnUVZ_jGdnZ2d@sasktel...



Thank you so Much Spikey, was able to do exactly what I was looking for
and more :-)

my version of your formula

=IF(SUM(COACCT2008!$C$4:$C$600)=0,"",IF((SUMPRODUC T((COACCT2008!$A$2:$A$300*0=A5)*(COACCT2008!$B$2:$ B$3000=B5),COACCT2008!$C$2:$C$3000))=0,"",SUMPRODU C*T((COACCT2008!$A$2:$A$3000=A5)*(COACCT2008!$B$2: $B$3000=B5),COACCT2008!$C$2*:$C$3000*-1)))

works like a charm!!

"Spiky" wrote in message
...
On Apr 25, 1:39 pm, "phuser" wrote:
I would like to specify the column in the formula, there will be no
user
interaction and it is a financial schedule, and I do have another
worksheet
that adds up fields but I was able to get that one easily because I
only
had
to match field A to A using SUMIF, what I am unable to do is write up
the
formula to give me in worksheet1 C4 from worksheet2: gl (A) dept (B)
for
Nov
LY (C) then worksheet1 D4 gl (A) dept (B) Nov_Budget (D) etc etc.

Make sense?

"Spiky" wrote in message

...

On Apr 25, 12:22 pm, "phuser" wrote:
I want to return a value based on column A & B, all the formulas I
know
or
have found only allow for a value to be returned when the amount
is
based
in
1 column but as you can see below I have LY, Budget & TY, for each
month,
I
need to specify the column to be returned. This is one of the
formula's
I've
tried.

=INDEX(COACCT2008!$C$2:$AL$3000,
MATCH(1,(A4=COACCT2008!$A$2:$A$3000)*(B4=COACCT200 8!$B$2:$B$3000),0))

GL_ACCT GL_DEPT NOV_1YR NOV_BUDGET NOV_CURRENT DEC_1YR
DEC_BUDGET
DEC_CURRENT JAN_1YR
4010 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA01 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA03 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA08 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA09 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA14 -99.36 0.00 0.00 0.00 0.00 0.00 -397.44
4010 PA15 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA23 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 PA24 0.00 0.00 0.00 0.00 0.00 0.00 0.00
4010 RG02 0.00 0.00 0.00 0.00 0.00 0.00 0.00

Need more info I think. Like...
How do you want to specify the column to retrieve data from? Will
you
type it in once? Or does it require some user input or some other
changing reference? Do you want a different formula to look up each
column, like in a financial schedule of some kind? Ever need to add
multiple columns?

I guess....what exactly are you trying to create?

I think this works.
=SUMPRODUCT((A1:A12=4010)*(B1:B12="PA14"),C1:C12)

This chooses your account from column A, your dept from column B, then
returns the item in column C. You can adjust the formula to do one for
each column you want to reference by changing the C1:C12 range. If the
account numbers are text, put quotes around it instead of just the
number (so: "4010"). Also, the ranges must be identical in size. So,
A1:A12 and B2:B12 will not work in the same formula.- Hide quoted
text -

- Show quoted text -