software

What I'd like to to is the following.

I have many rows, some with data in column A and some with data in column B.
I'd like to do is if there is data in BOTH column A AND B for the same row to
toltal those figures for the entire sheet.

I'm not sure how to write the formula to validate that there is both data in
column A AND column B. Any help is appreciated


This formula multiplies each column A by respective column B and
cumulatively sums those products only for the rows containing values in
Column A and B.

=SUMPRODUCT(--(A1:A3lt;gt;quot;quot;),--(B1:B3lt;gt;quot;quot;),A1:A3,B1:B3)

Adjust ranges to suit.--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=503884=sumproduct(--(A1:A500lt;gt;quot;quot;),--(B1:B500lt;gt;quot;quot;),(A1:A500) (B1:B500))

--
Regards,
Tom Ogilvyquot;Aaron Saulisberryquot; gt; wrote in
message ...
gt; What I'd like to to is the following.
gt;
gt; I have many rows, some with data in column A and some with data in column
B.
gt; I'd like to do is if there is data in BOTH column A AND B for the same row
to
gt; toltal those figures for the entire sheet.
gt;
gt; I'm not sure how to write the formula to validate that there is both data
in
gt; column A AND column B. Any help is appreciated
Hi,

You can add another column say C and enter this formula there

=IF(AND(A1lt;gt;quot;quot;,B1lt;gt;quot;quot;),A1 B1,0)

This formula would sumup column A and B, only when they both are not blank.

Then you can sumup column C and find out the total.

Regards

Govind.

Aaron Saulisberry wrote:

gt; What I'd like to to is the following.
gt;
gt; I have many rows, some with data in column A and some with data in column B.
gt; I'd like to do is if there is data in BOTH column A AND B for the same row to
gt; toltal those figures for the entire sheet.
gt;
gt; I'm not sure how to write the formula to validate that there is both data in
gt; column A AND column B. Any help is appreciated

Hi!

Try this:

=SUMPRODUCT((A1:A10lt;gt;quot;quot;)*(B1:B10lt;gt;quot;quot;)*A1:B10)

Biff

quot;Aaron Saulisberryquot; gt; wrote in
message ...
gt; What I'd like to to is the following.
gt;
gt; I have many rows, some with data in column A and some with data in column
gt; B.
gt; I'd like to do is if there is data in BOTH column A AND B for the same row
gt; to
gt; toltal those figures for the entire sheet.
gt;
gt; I'm not sure how to write the formula to validate that there is both data
gt; in
gt; column A AND column B. Any help is appreciated
Thanks for the quick respone.... it works!

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT((A1:A10lt;gt;quot;quot;)*(B1:B10lt;gt;quot;quot;)*A1:B10)
gt;
gt; Biff
gt;
gt; quot;Aaron Saulisberryquot; gt; wrote in
gt; message ...
gt; gt; What I'd like to to is the following.
gt; gt;
gt; gt; I have many rows, some with data in column A and some with data in column
gt; gt; B.
gt; gt; I'd like to do is if there is data in BOTH column A AND B for the same row
gt; gt; to
gt; gt; toltal those figures for the entire sheet.
gt; gt;
gt; gt; I'm not sure how to write the formula to validate that there is both data
gt; gt; in
gt; gt; column A AND column B. Any help is appreciated
gt;
gt;
gt;

Well I hit the reply button to soon...

The formula does work but it returns the count of how many matches there are
rather than the total value of the columns that match. As always, help is
greatly appreciated.

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT((A1:A10lt;gt;quot;quot;)*(B1:B10lt;gt;quot;quot;)*A1:B10)
gt;
gt; Biff
gt;
gt; quot;Aaron Saulisberryquot; gt; wrote in
gt; message ...
gt; gt; What I'd like to to is the following.
gt; gt;
gt; gt; I have many rows, some with data in column A and some with data in column
gt; gt; B.
gt; gt; I'd like to do is if there is data in BOTH column A AND B for the same row
gt; gt; to
gt; gt; toltal those figures for the entire sheet.
gt; gt;
gt; gt; I'm not sure how to write the formula to validate that there is both data
gt; gt; in
gt; gt; column A AND column B. Any help is appreciated
gt;
gt;
gt;

Which formula did you use?

Post the exact formula you tried.

Biff

quot;Aaron Saulisberryquot; gt; wrote in
message ...
gt; Well I hit the reply button to soon...
gt;
gt; The formula does work but it returns the count of how many matches there
gt; are
gt; rather than the total value of the columns that match. As always, help is
gt; greatly appreciated.
gt;
gt; quot;Biffquot; wrote:
gt;
gt;gt; Hi!
gt;gt;
gt;gt; Try this:
gt;gt;
gt;gt; =SUMPRODUCT((A1:A10lt;gt;quot;quot;)*(B1:B10lt;gt;quot;quot;)*A1:B10)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Aaron Saulisberryquot; gt; wrote in
gt;gt; message ...
gt;gt; gt; What I'd like to to is the following.
gt;gt; gt;
gt;gt; gt; I have many rows, some with data in column A and some with data in
gt;gt; gt; column
gt;gt; gt; B.
gt;gt; gt; I'd like to do is if there is data in BOTH column A AND B for the same
gt;gt; gt; row
gt;gt; gt; to
gt;gt; gt; toltal those figures for the entire sheet.
gt;gt; gt;
gt;gt; gt; I'm not sure how to write the formula to validate that there is both
gt;gt; gt; data
gt;gt; gt; in
gt;gt; gt; column A AND column B. Any help is appreciated
gt;gt;
gt;gt;
gt;gt;