Sum of values where another column meets criteria 2

[Smoothas]

Hello

I have a spreadsheet were Column A contains peoples names , and column G contains a number.
I'm trying to figure out a way of "summing" the cells in column G with the coresponding cell in Column A is NOT blank.
I've tried the SUMIF and SUMPRODUCT commands, but all I seem to get is #NULL.

=SUMPRODUCT(ISBLANK(A1:A64999)*G1:C64999) gives me a value of 0, which I beleiev is correct as all the cells in A have a textual value in them at the moment.
I tried using ISTEXT"" , but got #VALUE as a responce.
Can anybody help me ?

Thanks in advance
Gerald

 

[TonyJollans]

This works for me:

[blue][tt]=SUMIF(A:A,">""",G:G)[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

...or
[tt]
=SUMPRODUCT((A1:A64999="")*(G1:G64999))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

Thanks for the quick response.
Tony : your code worked like a charm. Thank you
Skip: yours gave me a #VALUE "error"

Thank you both for your effort

 

[SkipVought]

Change YOUR range to fit the data. SUMPRODUCT is more flexible at times but has some limitations. It almost NEVER use direct range references. Rather named ranges that define the range of the data.

Do you, for instance, have headings in row 1? In fact ANY text in G1:G64999 will result in an error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

Ohh, thanks for explaining that. works like a charm now. Doh.

On a tangent with this, is there a way to tweak the code so that if, say, on the same row, if Column A's cell = "LONBIC" and Column D's ="LEGAL" , that is would SUM the corresponding total in Column G (which contains only numerical data )?

I've being trying to do this with both sets of code, but not getting any posative results.

Thanks again

 

[SkipVought]

[tt]
=SUMPRODUCT((A1:A64999="LONBIC")*(D1641999="LEGAL")*(G1:G64999))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

Thanks for the quick responce skip.
tried your code, but got #VALUE as the result.
Remebering what you said earlier, I change G1 to G2 ( as G1 contains the column title ), and got #N/A instead.

Can you tell me what i'm doing wrong again?
Thanks
Gerald

 

[SkipVought]

Search down thru your column G data for invalid values. I'd suggest using the AutoFilter. You can identify errors quickly.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

GENIUS Skip. So obvious now.
I changed all the "A1"s to A2 ( as row 1 is Column headers), and PRESTO.

Thanks again for your help Skip.
You can always rely on Tek-Tips and the forum memebers to help people out.

 

[SkipVought]

Glad to share!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}