How would this formula read

[Guest]

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!!!

 

[Guest]

=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

 

[Guest]

Hi Duke...

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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!!!!!