Compare cell = one result

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:A3<>""),--(B1:B3<>""),A1:A3,B1:B3)

Adjust ranges to suit.

Hi,

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

=IF(AND(A1<>"",B1<>""),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:

> 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

=sumproduct(--(A1:A500<>""),--(B1:B500<>""),(A1:A500)+(B1:B500))

--
Regards,
Tom Ogilvy


"Aaron Saulisberry" <AaronSaulisberry@discussions.microsoft.com> wrote in
message news:3359ACC3-EFD5-4179-B3C1-7C1B5ACF75B8@microsoft.com...
> 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

Hi!

Try this:

=SUMPRODUCT((A1:A10<>"")*(B1:B10<>"")*A1:B10)

Biff

"Aaron Saulisberry" <AaronSaulisberry@discussions.microsoft.com> wrote in
message news:3359ACC3-EFD5-4179-B3C1-7C1B5ACF75B8@microsoft.com...
> 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

Thanks for the quick respone.... it works!

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMPRODUCT((A1:A10<>"")*(B1:B10<>"")*A1:B10)
>
> Biff
>
> "Aaron Saulisberry" <AaronSaulisberry@discussions.microsoft.com> wrote in
> message news:3359ACC3-EFD5-4179-B3C1-7C1B5ACF75B8@microsoft.com...
> > 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
>
>
>

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.

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMPRODUCT((A1:A10<>"")*(B1:B10<>"")*A1:B10)
>
> Biff
>
> "Aaron Saulisberry" <AaronSaulisberry@discussions.microsoft.com> wrote in
> message news:3359ACC3-EFD5-4179-B3C1-7C1B5ACF75B8@microsoft.com...
> > 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
>
>
>

Which formula did you use?

Post the exact formula you tried.

Biff

"Aaron Saulisberry" <AaronSaulisberry@discussions.microsoft.com> wrote in
message news:712E9A6D-FDF2-48E8-8583-B047AC04C08C@microsoft.com...
> 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.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> =SUMPRODUCT((A1:A10<>"")*(B1:B10<>"")*A1:B10)
>>
>> Biff
>>
>> "Aaron Saulisberry" <AaronSaulisberry@discussions.microsoft.com> wrote in
>> message news:3359ACC3-EFD5-4179-B3C1-7C1B5ACF75B8@microsoft.com...
>> > 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
>>
>>
>>