Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in the
range of B1:B8 are ignored; thus, not producing a #Value! error?
=SUMPRODUCT((A1:A8="a")*(B1:B8))
Thanks in advance,
Ricky
Hello Experts,
How would you modify this formula so that the text and ="" [blanks] in the
range of B1:B8 are ignored; thus, not producing a #Value! error?
=SUMPRODUCT((A1:A8="a")*(B1:B8))
Thanks in advance,
Ricky
Try...
=SUMPRODUCT(--(A1:A8="a"),B1:B8)
Hope this helps!
In article <#kfBVzg#[email protected]>,
"Ricky Pang" <[email protected]> wrote:
> Hello Experts,
> How would you modify this formula so that the text and ="" [blanks] in the
> range of B1:B8 are ignored; thus, not producing a #Value! error?
>
> =SUMPRODUCT((A1:A8="a")*(B1:B8))
>
> Thanks in advance,
> Ricky
Thanks Domenic,
That worked nicely.
Ricky
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> =SUMPRODUCT(--(A1:A8="a"),B1:B8)
>
> Hope this helps!
>
> In article <#kfBVzg#[email protected]>,
> "Ricky Pang" <[email protected]> wrote:
>
>> Hello Experts,
>> How would you modify this formula so that the text and ="" [blanks] in
>> the
>> range of B1:B8 are ignored; thus, not producing a #Value! error?
>>
>> =SUMPRODUCT((A1:A8="a")*(B1:B8))
>>
>> Thanks in advance,
>> Ricky
Ricky Pang wrote:
> Hello Experts,
> How would you modify this formula so that the text and ="" [blanks] in the
> range of B1:B8 are ignored; thus, not producing a #Value! error?
>
> =SUMPRODUCT((A1:A8="a")*(B1:B8))
>
> Thanks in advance,
> Ricky
>
>
If this is not a question about the behavior of SumProduct with a
contrieved example...
=SUMIF((A1:A8,"a",B1:B8)
will suffice.
Hello again,
How would you expand on this formula (which excludes text and "") so that
multiple data columns ranging from B1:H8 would be included in the result
once the criteria of ="a" has been established? Presently, only the data in
B1:B8 works. I need to expand the range.
=sumproduct(--(A1:A8="a"),B1:B8)
Thanks in advance,
Ricky
"Ricky Pang" <[email protected]> wrote in message
news:e0m18Nh%[email protected]...
> Thanks Domenic,
> That worked nicely.
>
> Ricky
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
>> Try...
>>
>> =SUMPRODUCT(--(A1:A8="a"),B1:B8)
>>
>> Hope this helps!
>>
>> In article <#kfBVzg#[email protected]>,
>> "Ricky Pang" <[email protected]> wrote:
>>
>>> Hello Experts,
>>> How would you modify this formula so that the text and ="" [blanks] in
>>> the
>>> range of B1:B8 are ignored; thus, not producing a #Value! error?
>>>
>>> =SUMPRODUCT((A1:A8="a")*(B1:B8))
>>>
>>> Thanks in advance,
>>> Ricky
>
>
Try...
=SUM(IF(A1:A8="a",B1:H8))
....confirmed with CONROL+SHIFT+ENTER, not just ENTER.
Hope this helps!
In article <eX#[email protected]>,
"Ricky Pang" <[email protected]> wrote:
> Hello again,
> How would you expand on this formula (which excludes text and "") so that
> multiple data columns ranging from B1:H8 would be included in the result
> once the criteria of ="a" has been established? Presently, only the data in
> B1:B8 works. I need to expand the range.
>
> =sumproduct(--(A1:A8="a"),B1:B8)
>
> Thanks in advance,
> Ricky
Hi Domenic,
Thank-you once again. It worked. This helps me a great deal.
Ricky
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> =SUM(IF(A1:A8="a",B1:H8))
>
> ...confirmed with CONROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <eX#[email protected]>,
> "Ricky Pang" <[email protected]> wrote:
>
>> Hello again,
>> How would you expand on this formula (which excludes text and "") so that
>> multiple data columns ranging from B1:H8 would be included in the result
>> once the criteria of ="a" has been established? Presently, only the data
>> in
>> B1:B8 works. I need to expand the range.
>>
>> =sumproduct(--(A1:A8="a"),B1:B8)
>>
>> Thanks in advance,
>> Ricky
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks