How would this formula read

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