Hi All...

I am trying to figure out what the best way to perform the following
function would be.
On overview - I am trying to have Excel look for the word "Total" in column
B, and the word "Asset" in column C, and if both of those items are
satisfied, I want it to return the value (which is a number)from column H....

1. Which function would do that for me?
2. What would the formula look like?

Thanks!!!

=sumproduct(--(B1:b500="Total"),--(C1:c500="Asset"),H1:h500)

This will return the SUM of column H for each row where B=Total and C=Asset.
If there's only one entry to match those criteria, then this is all you need


"Frantic Excel-er" wrote:

Hi All...

I am trying to figure out what the best way to perform the following
function would be.
On overview - I am trying to have Excel look for the word "Total" in column
B, and the word "Asset" in column C, and if both of those items are
satisfied, I want it to return the value (which is a number)from column H....

1. Which function would do that for me?
2. What would the formula look like?

Thanks!!!

Hi Duke...

copied the formula, and it is still giving me a "0" value.....
any suggestions?

"Duke Carey" wrote:

=sumproduct(--(B1:b500="Total"),--(C1:c500="Asset"),H1:h500)

This will return the SUM of column H for each row where B=Total and C=Asset.
If there's only one entry to match those criteria, then this is all you need


"Frantic Excel-er" wrote:

Hi All...

I am trying to figure out what the best way to perform the following
function would be.
On overview - I am trying to have Excel look for the word "Total" in column
B, and the word "Asset" in column C, and if both of those items are
satisfied, I want it to return the value (which is a number)from column H....

1. Which function would do that for me?
2. What would the formula look like?

Thanks!!!

In the excel guide, it says that if a cell is not numeric, then excel will
treat that like a zero...which means it is taking my total and multiplying it
to zero, which is giving me a zero. ----any other suggestions?

"Duke Carey" wrote:

=sumproduct(--(B1:b500="Total"),--(C1:c500="Asset"),H1:h500)

This will return the SUM of column H for each row where B=Total and C=Asset.
If there's only one entry to match those criteria, then this is all you need


"Frantic Excel-er" wrote:

Hi All...

I am trying to figure out what the best way to perform the following
function would be.
On overview - I am trying to have Excel look for the word "Total" in column
B, and the word "Asset" in column C, and if both of those items are
satisfied, I want it to return the value (which is a number)from column H....

1. Which function would do that for me?
2. What would the formula look like?

Thanks!!!

With made up data on my worksheet it functions just fine. My first guess is
that your values for "Total" & "Assets" may have spaces before or after.

To troubleshoot: Find one of the cells that you think contains "Total" and
in an empty cell nearby type

="Total" = that cell

if you get a true, try the same tst with "Assets". If that comes back true,
then I'd suspect the values in col H aren't really numbers, but text
representations of numbers.

After that, I'm out of guesses


"Frantic Excel-er" wrote:

Hi Duke...

copied the formula, and it is still giving me a "0" value.....
any suggestions?

"Duke Carey" wrote:

=sumproduct(--(B1:b500="Total"),--(C1:c500="Asset"),H1:h500)

This will return the SUM of column H for each row where B=Total and C=Asset.
If there's only one entry to match those criteria, then this is all you need


"Frantic Excel-er" wrote:

Hi All...

I am trying to figure out what the best way to perform the following
function would be.
On overview - I am trying to have Excel look for the word "Total" in column
B, and the word "Asset" in column C, and if both of those items are
satisfied, I want it to return the value (which is a number)from column H....

1. Which function would do that for me?
2. What would the formula look like?

Thanks!!!

I think that the sumproduct is returning a zero value for my text
cells....which is giving me a zero value for my product....I am using an
older version of excel, and so this might be a function that has been
upgraded in newer versions.

However, I did get excel to calculate this for me using the SUMIF function,
and it works beautifully...

I wouldn't have figured it out without your help though....thanks a bunch
Duke!!!!!

"Duke Carey" wrote:

With made up data on my worksheet it functions just fine. My first guess is
that your values for "Total" & "Assets" may have spaces before or after.

To troubleshoot: Find one of the cells that you think contains "Total" and
in an empty cell nearby type

="Total" = that cell

if you get a true, try the same tst with "Assets". If that comes back true,
then I'd suspect the values in col H aren't really numbers, but text
representations of numbers.

After that, I'm out of guesses


"Frantic Excel-er" wrote:

Hi Duke...

copied the formula, and it is still giving me a "0" value.....
any suggestions?

"Duke Carey" wrote:

=sumproduct(--(B1:b500="Total"),--(C1:c500="Asset"),H1:h500)

This will return the SUM of column H for each row where B=Total and C=Asset.
If there's only one entry to match those criteria, then this is all you need


"Frantic Excel-er" wrote:

Hi All...

I am trying to figure out what the best way to perform the following
function would be.
On overview - I am trying to have Excel look for the word "Total" in column
B, and the word "Asset" in column C, and if both of those items are
satisfied, I want it to return the value (which is a number)from column H....

1. Which function would do that for me?
2. What would the formula look like?

Thanks!!!