Hi there,

I'm having an issue with a formula of mine. My data is 5 columns wide. I
have the formula listed below to retrieve the value of column C if column A
matches a set value. Unless column E states "VOID". Then I want the value
input to be "Void". The formula kinda works. It gets the value of column C
if column E is not void, but if column E states void then I get a 0. Column
E is a formula in itself looking at column D for a value of 1. I tried
changing the formula below to search column D instead of E for a 1 instead of
a void and then it returns the value of column C regardless of what it finds.

{=SUM(IF('Cheque Register'!A$2:A$195=B47,IF('Cheque
Register'!E$2:E$195="VOID","VOID",'Cheque Register'!C$2:C$195),0))}

I'm not sure what I'm missing, the logic seems clear to me, but obviously
the computer thinks otherwise. If anybody out there can help me I'd
appreciate it.

Thanks,
Shauna

I think that you want

=IF(ISNUMBER(MATCH("VOID",'Cheque Register'!E$2:E$195)),"VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
Hi there,

I'm having an issue with a formula of mine. My data is 5 columns wide. I
have the formula listed below to retrieve the value of column C if column
A
matches a set value. Unless column E states "VOID". Then I want the value
input to be "Void". The formula kinda works. It gets the value of column
C
if column E is not void, but if column E states void then I get a 0.
Column
E is a formula in itself looking at column D for a value of 1. I tried
changing the formula below to search column D instead of E for a 1 instead
of
a void and then it returns the value of column C regardless of what it
finds.

{=SUM(IF('Cheque Register'!A$2:A$195=B47,IF('Cheque
Register'!E$2:E$195="VOID","VOID",'Cheque Register'!C$2:C$195),0))}

I'm not sure what I'm missing, the logic seems clear to me, but obviously
the computer thinks otherwise. If anybody out there can help me I'd
appreciate it.

Thanks,
Shauna

Thank you very much! Worked great.

"Bob Phillips" wrote:

I think that you want

=IF(ISNUMBER(MATCH("VOID",'Cheque Register'!E$2:E$195)),"VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
Hi there,

I'm having an issue with a formula of mine. My data is 5 columns wide. I
have the formula listed below to retrieve the value of column C if column
A
matches a set value. Unless column E states "VOID". Then I want the value
input to be "Void". The formula kinda works. It gets the value of column
C
if column E is not void, but if column E states void then I get a 0.
Column
E is a formula in itself looking at column D for a value of 1. I tried
changing the formula below to search column D instead of E for a 1 instead
of
a void and then it returns the value of column C regardless of what it
finds.

{=SUM(IF('Cheque Register'!A$2:A$195=B47,IF('Cheque
Register'!E$2:E$195="VOID","VOID",'Cheque Register'!C$2:C$195),0))}

I'm not sure what I'm missing, the logic seems clear to me, but obviously
the computer thinks otherwise. If anybody out there can help me I'd
appreciate it.

Thanks,
Shauna

OOps, I've copied the formula down and now I get a void where there
shouldn't be voids but should be values. Any thoughts?

"shaunap" wrote:

Thank you very much! Worked great.

"Bob Phillips" wrote:

I think that you want

=IF(ISNUMBER(MATCH("VOID",'Cheque Register'!E$2:E$195)),"VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
Hi there,

I'm having an issue with a formula of mine. My data is 5 columns wide. I
have the formula listed below to retrieve the value of column C if column
A
matches a set value. Unless column E states "VOID". Then I want the value
input to be "Void". The formula kinda works. It gets the value of column
C
if column E is not void, but if column E states void then I get a 0.
Column
E is a formula in itself looking at column D for a value of 1. I tried
changing the formula below to search column D instead of E for a 1 instead
of
a void and then it returns the value of column C regardless of what it
finds.

{=SUM(IF('Cheque Register'!A$2:A$195=B47,IF('Cheque
Register'!E$2:E$195="VOID","VOID",'Cheque Register'!C$2:C$195),0))}

I'm not sure what I'm missing, the logic seems clear to me, but obviously
the computer thinks otherwise. If anybody out there can help me I'd
appreciate it.

Thanks,
Shauna

I had trouble following your explanation, but in the light of your last
post, perhaps you just want

=IF('Cheque Register'!E2="VOID","VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

and copy that down.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
OOps, I've copied the formula down and now I get a void where there
shouldn't be voids but should be values. Any thoughts?

"shaunap" wrote:

Thank you very much! Worked great.

"Bob Phillips" wrote:

I think that you want

=IF(ISNUMBER(MATCH("VOID",'Cheque Register'!E$2:E$195)),"VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
Hi there,

I'm having an issue with a formula of mine. My data is 5 columns
wide. I
have the formula listed below to retrieve the value of column C if
column
A
matches a set value. Unless column E states "VOID". Then I want the
value
input to be "Void". The formula kinda works. It gets the value of
column
C
if column E is not void, but if column E states void then I get a 0.
Column
E is a formula in itself looking at column D for a value of 1. I
tried
changing the formula below to search column D instead of E for a 1
instead
of
a void and then it returns the value of column C regardless of what
it
finds.

{=SUM(IF('Cheque Register'!A$2:A$195=B47,IF('Cheque
Register'!E$2:E$195="VOID","VOID",'Cheque Register'!C$2:C$195),0))}

I'm not sure what I'm missing, the logic seems clear to me, but
obviously
the computer thinks otherwise. If anybody out there can help me I'd
appreciate it.

Thanks,
Shauna

First I'd like to say I really appreciate your help with all this. I've
noticed that you are on here alot and are "THE" guru.

Maybe if I gave a better explanation of what I'm after. I have a sheet of
data as follows:

(A) (B) (C) (D) (E)
Chq #'s paid to Chq $ indicating void formula input
"VOID"
123 xyz 98.24 0
124 abc 212.50 1 VOID
126 mno 648.50 1 VOID
126 mno 1648.50 0
132 jkl 99.65 0


That give an idea of my data. I had the formula I first listed as a result
of a previous post to the newsgroups here. Dave was nice enough to help me
out and actually directed me to your site on SUMproduct. I am still quite
hazy on sumproduct but got the SUMIF function to work for me. Now I'm trying
to tweak it more so instead of getting a 0 returned for items that are voided
and not relisted but to get a return value of void.

My return data list is a numerical listing of cheques so that I can see
which ones are not accounted for so I can have a short list of numbers to
hunt down. Also so I have a running total of cheques issued for the month.

Let me know if this makes any more sense. The logic in the initial formula
I posted on this thread still makes sense but it's just not returning what I
figure it should.

Thank you.

Shauna

"Bob Phillips" wrote:

I had trouble following your explanation, but in the light of your last
post, perhaps you just want

=IF('Cheque Register'!E2="VOID","VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

and copy that down.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
OOps, I've copied the formula down and now I get a void where there
shouldn't be voids but should be values. Any thoughts?

"shaunap" wrote:

Thank you very much! Worked great.

"Bob Phillips" wrote:

I think that you want

=IF(ISNUMBER(MATCH("VOID",'Cheque Register'!E$2:E$195)),"VOID",
SUMIF('Cheque Register'!A$2:A$195,B47,'Cheque Register'!C$2:C$195))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"shaunap" wrote in message
...
Hi there,

I'm having an issue with a formula of mine. My data is 5 columns
wide. I
have the formula listed below to retrieve the value of column C if
column
A
matches a set value. Unless column E states "VOID". Then I want the
value
input to be "Void". The formula kinda works. It gets the value of
column
C
if column E is not void, but if column E states void then I get a 0.
Column
E is a formula in itself looking at column D for a value of 1. I
tried
changing the formula below to search column D instead of E for a 1
instead
of
a void and then it returns the value of column C regardless of what
it
finds.

{=SUM(IF('Cheque Register'!A$2:A$195=B47,IF('Cheque
Register'!E$2:E$195="VOID","VOID",'Cheque Register'!C$2:C$195),0))}

I'm not sure what I'm missing, the logic seems clear to me, but
obviously
the computer thinks otherwise. If anybody out there can help me I'd
appreciate it.

Thanks,
Shauna