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]

Thank you very much! Worked great.

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]

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?

 

[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]

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