SUM(IF) question

[shaunap]

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

[Bob Phillips]

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" <shaunap@discussions.microsoft.com> wrote in message
news:7F142A94-88C2-4F4D-9C4C-BE08ED7A967A@microsoft.com...
> 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

[shaunap]

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" <shaunap@discussions.microsoft.com> wrote in message
> news:7F142A94-88C2-4F4D-9C4C-BE08ED7A967A@microsoft.com...
> > 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
>
>
>

[shaunap]

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" <shaunap@discussions.microsoft.com> wrote in message
> > news:7F142A94-88C2-4F4D-9C4C-BE08ED7A967A@microsoft.com...
> > > 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
> >
> >
> >

[Bob Phillips]

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" <shaunap@discussions.microsoft.com> wrote in message
news:FC5E5F92-E072-4905-A049-BB39384E94C1@microsoft.com...
> 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" <shaunap@discussions.microsoft.com> wrote in message
> > > news:7F142A94-88C2-4F4D-9C4C-BE08ED7A967A@microsoft.com...
> > > > 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
> > >
> > >
> > >

[shaunap]

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" <shaunap@discussions.microsoft.com> wrote in message
> news:FC5E5F92-E072-4905-A049-BB39384E94C1@microsoft.com...
> > 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" <shaunap@discussions.microsoft.com> wrote in message
> > > > news:7F142A94-88C2-4F4D-9C4C-BE08ED7A967A@microsoft.com...
> > > > > 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
> > > >
> > > >
> > > >
>
>
>