conditional formula: sum a range if text present in another range

SUMMARY
Conditional Formula: If Worksheet1 cells A1-5 have text, then I want to Sum
Worksheet2 nonadjacent cells. I want results in Worksheet3 cell D4.

I'm trying to create a formula to populate a range of cells, but only if
text exist.

Example: If any cell from A1-A5 has "Yes," then add cells F4, F6, and F8.

Ultimately, I'd actually would like to populate a range of cells with
summation results, if other cells have text ... but for now, a simple formula
would do.

Any experts out there ... please help. Much thanks.

Hi!

Try this:

=IF(COUNTIF(Sheet1!A1:A5,"Yes"),Sheet2!F4+Sheet2!F6+Sheet2!F8,"")

Biff

"NeedAdvice777" <NeedAdvice777@discussions.microsoft.com> wrote in message
news:FC881B50-B1C8-4EAF-B27F-EF5DDD4ADF64@microsoft.com...
> SUMMARY
> Conditional Formula: If Worksheet1 cells A1-5 have text, then I want to
> Sum
> Worksheet2 nonadjacent cells. I want results in Worksheet3 cell D4.
>
> I'm trying to create a formula to populate a range of cells, but only if
> text exist.
>
> Example: If any cell from A1-A5 has "Yes," then add cells F4, F6, and F8.
>
> Ultimately, I'd actually would like to populate a range of cells with
> summation results, if other cells have text ... but for now, a simple
> formula
> would do.
>
> Any experts out there ... please help. Much thanks.

It works if I use only Sheet2!F4, but not if I use the cell range. Same
problem I've been having.


"Biff" wrote:

> Hi!
>
> Try this:
>
> =IF(COUNTIF(Sheet1!A1:A5,"Yes"),Sheet2!F4+Sheet2!F6+Sheet2!F8,"")
>
> Biff
>
> "NeedAdvice777" <NeedAdvice777@discussions.microsoft.com> wrote in message
> news:FC881B50-B1C8-4EAF-B27F-EF5DDD4ADF64@microsoft.com...
> > SUMMARY
> > Conditional Formula: If Worksheet1 cells A1-5 have text, then I want to
> > Sum
> > Worksheet2 nonadjacent cells. I want results in Worksheet3 cell D4.
> >
> > I'm trying to create a formula to populate a range of cells, but only if
> > text exist.
> >
> > Example: If any cell from A1-A5 has "Yes," then add cells F4, F6, and F8.
> >
> > Ultimately, I'd actually would like to populate a range of cells with
> > summation results, if other cells have text ... but for now, a simple
> > formula
> > would do.
> >
> > Any experts out there ... please help. Much thanks.
>
>
>

"NeedAdvice777" <NeedAdvice777@discussions.microsoft.com> wrote in message
news:10AD22A0-1DB6-4D8D-B162-C3B96BDFC44F@microsoft.com...
> It works if I use only Sheet2!F4, but not if I use the cell range. Same
> problem I've been having.

Post the *EXACT* formula you tried.

You say: "It works if I use only Sheet2!F4, but not if I use the cell
range."

What does it do when it doesn't work? Get an error? Incorrect result?

Biff

Hi Biff,

One of the exact formulas is:
=SUM(IF(Daily Consumption!$B$5:$B$52>0,$C$2:$C$50,0))
It gives error:
#NAME?

I've tried other formulas ... let me back up and tell you my motive ... to
get healthier ... just for a while I'd like to keep track of nutrient intake.
So if I take my vitamins ... I mark that column and it will automatically
fill in another column with the values of nutrients ... If I eat a hot dog
.... it automatically tallies those nutrients in appropriate column ... i.e.
sodium intake. It's a complex formula that may never wholly work properly,
but it also is an Excel learning curve and challenge.

BTW: I've already got the daily requirements in one column and the
individual breakdown of my vitamin components in another. I am adding such
things as hotdogs and buns to another.

Thanks if you can help.

This will almost work:
=SUM(IF('Daily Consumption'!$B$5:$B$52>0,$C$2:$C$50,0))
or
=sumproduct(--('Daily Consumption'!$B$5:$B$52>0),$C$2:$C$50)

But you're going to have to adjust those ranges to have the same number of
cells. Currently, you have 48 in column B and 49 in column C.

And did you really mean to use data on different sheets?

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

NeedAdvice777 wrote:
>
> Hi Biff,
>
> One of the exact formulas is:
> =SUM(IF(Daily Consumption!$B$5:$B$52>0,$C$2:$C$50,0))
> It gives error:
> #NAME?
>
> I've tried other formulas ... let me back up and tell you my motive ... to
> get healthier ... just for a while I'd like to keep track of nutrient intake.
> So if I take my vitamins ... I mark that column and it will automatically
> fill in another column with the values of nutrients ... If I eat a hot dog
> ... it automatically tallies those nutrients in appropriate column ... i.e.
> sodium intake. It's a complex formula that may never wholly work properly,
> but it also is an Excel learning curve and challenge.
>
> BTW: I've already got the daily requirements in one column and the
> individual breakdown of my vitamin components in another. I am adding such
> things as hotdogs and buns to another.
>
> Thanks if you can help.

--

Dave Peterson