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

[Guest]

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.

 

[Biff]

Hi!

Try this:

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

Biff

 

[Guest]

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

 

[Biff]

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

 

[Guest]

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]

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

 

[Biff]

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.

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

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

 

[Guest]

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.

F6+Sheet2!F8,"")


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

Biff

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.


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]

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/assistance/2002/articles/xlconPT101.aspx

 

[Guest]

I apologize for the very slow response back ... I had been giving this some
thought and you confirmed what I was leaning towards trying.

I will let you know how this worked out.

As always, thanks for the tip, Dave.

 

[Dave Peterson]

Good luck.

I apologize for the very slow response back ... I had been giving this some
thought and you confirmed what I was leaning towards trying.

I will let you know how this worked out.

As always, thanks for the tip, Dave.