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

Dave pretty much covered things! But.......

>Conditional Formula: If Worksheet1 cells A1-5 have text, then I want to Sum
>Worksheet2 nonadjacent cells. I want results in Worksheet3 cell D4.
>Example: If any cell from A1-A5 has "Yes," then add cells F4, F6, and F8.

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

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

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

........the formula I suggested DOES EXACTLY WHAT YOU ASKED FOR!

<VBG>

Biff

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44B2C4C3.5A767376@verizonXSPAM.net...
> 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

Let me first say, thank you both for all your assistance, Biff and Dave.

Unfortunately, the below formula DOES NOT do exactly what I want. I wish it
did.

> >>=IF(COUNTIF(Sheet1!A1:A5,"Yes"),Sheet2!F4+Sheet2!
F6+Sheet2!F8,"")
>
> > 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.
>
>
> ........the formula I suggested DOES EXACTLY WHAT YOU ASKED FOR!
>

> Biff
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message


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

Believe it or not, Dave, yes I did mean to use different sheets. But if this
is a bad idea, then I will put the information on one sheet. I know enough
about Excel to be dangerous, but not enough to be proficient. So ... if you
suggest one data sheet, then I will try it.

THE REASON I WANTED different sheets is because the data is sooooo
cumbersome on just one sheet. I was trying to make it "user friendly" for
myself.

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


I tried your formulas and reworked them to what I think for my multiple
sheets:

=SUM(IF(Calculator!$B$5:$B$52>0,'Daily Consumption'!$B$4:$B$51,0))

It's already cumbersome keeping track of initial intent. I am thinking an
array that fills in multiple data is actually what I want ...

My purpose ... keep track of daily vitamin and nutrition intake . So, I have
created one sheet with Daily Recommended Vitamins (I call Vitamin Check) from
Biotin to Zinc. I'd like to ultimately compare this information with my
actual consumption.

I created another sheet that actually has the vitamin content of Centrum,
and other vitamin supplements. I will add to this with such things as
chicken, hotdogs, ham, etc ... you get the picture: CUMBERSOME.

Anyway, I created a third sheet to tally underneath "each day of the week"
my vitamin intake of all things consumed that day. So, if all things worked
accordingly, when I checked off Centrum in the sheet I call Daily
Consumption, then my formula (or array) will look at my Calculator under the
Centrum column and put all that information into a fourth sheet (right now I
simply call Sheet3). It is meant to be a running tally for the day, each time
I check an item ... chicken, it will add the total protein value (or other
value) to the appropriate column in Sheet3.

I hope I am explaining myself well enough to give you a clear picture of my
intent. Like I said, I know only enough to be dangerous. But am having fun
strengthening my knowledge.

I am in the process of reading your suggested articles, Dave, and await to
hear any opinions regarding how best to achieve my goals.

Thanks again, Biff and Dave.

I think I'd try to put the data on one worksheet and in the same row. For my
brain, it just makes life much easier--and I don't have to worry about
inserting/deleting/sorting the other rows.

This may not be possible, but maybe a rearrangement of your data would be in
order.

I think I'd try to put the date in column A, the vitamin/mineral/element name in
column B, and the amount in column C.

Then I could use some kind of pivottable to display my per day (or per month
or...) summaries.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

NeedAdvice777 wrote:
>
> Let me first say, thank you both for all your assistance, Biff and Dave.
>
> Unfortunately, the below formula DOES NOT do exactly what I want. I wish it
> did.
>
> > >>=IF(COUNTIF(Sheet1!A1:A5,"Yes"),Sheet2!F4+Sheet2!
> F6+Sheet2!F8,"")
> >
> > > 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.
> >
> >
> > ........the formula I suggested DOES EXACTLY WHAT YOU ASKED FOR!
> >
>
> > Biff
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>
>
> > > And did you really mean to use data on different sheets?
>
> Believe it or not, Dave, yes I did mean to use different sheets. But if this
> is a bad idea, then I will put the information on one sheet. I know enough
> about Excel to be dangerous, but not enough to be proficient. So ... if you
> suggest one data sheet, then I will try it.
>
> THE REASON I WANTED different sheets is because the data is sooooo
> cumbersome on just one sheet. I was trying to make it "user friendly" for
> myself.
>
> > >
> > > 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.
> > >
>
> I tried your formulas and reworked them to what I think for my multiple
> sheets:
>
> =SUM(IF(Calculator!$B$5:$B$52>0,'Daily Consumption'!$B$4:$B$51,0))
>
> It's already cumbersome keeping track of initial intent. I am thinking an
> array that fills in multiple data is actually what I want ...
>
> My purpose ... keep track of daily vitamin and nutrition intake . So, I have
> created one sheet with Daily Recommended Vitamins (I call Vitamin Check) from
> Biotin to Zinc. I'd like to ultimately compare this information with my
> actual consumption.
>
> I created another sheet that actually has the vitamin content of Centrum,
> and other vitamin supplements. I will add to this with such things as
> chicken, hotdogs, ham, etc ... you get the picture: CUMBERSOME.
>
> Anyway, I created a third sheet to tally underneath "each day of the week"
> my vitamin intake of all things consumed that day. So, if all things worked
> accordingly, when I checked off Centrum in the sheet I call Daily
> Consumption, then my formula (or array) will look at my Calculator under the
> Centrum column and put all that information into a fourth sheet (right now I
> simply call Sheet3). It is meant to be a running tally for the day, each time
> I check an item ... chicken, it will add the total protein value (or other
> value) to the appropriate column in Sheet3.
>
> I hope I am explaining myself well enough to give you a clear picture of my
> intent. Like I said, I know only enough to be dangerous. But am having fun
> strengthening my knowledge.
>
> I am in the process of reading your suggested articles, Dave, and await to
> hear any opinions regarding how best to achieve my goals.
>
> Thanks again, Biff and Dave.

--

Dave Peterson