Help with excel formula

[dukeofperl]

I'm trying to sum columns based on criteria and not having a lot of luck.

So far I've tried SUM, SUMIF, SUMIFS with different criteria such as ISBLANK and all kinds of combinations -- my head is beginning to spin.

I know we have some black-belt excel folks on the forum so I'm appealing for some help.

A simplified spreadsheet can be viewed at:

http://myweb.cableone.net/skunkcreekcrew/spreadsheet.png

The rules
======

Read each cell in column E and if it is a blank cell, use the corresponding number in Column B as the number to sum.

If the cell in column E is non-blank, use the sum of the corresponding cells in columns C and D to be added to the sum.

Looking at my sample spreadsheet, I would SUM
1 + 9 (C1 + D1)
20 (B2)
3 + 7 (C3 + D3)
40 (B4)

the total would be 80

Any help with this is greatly appreciated.

duke

[Dave Schwartz]

The guy you want is either Raybo or Jon Hanover. Those guys appear to be amazing with Excel.

I would look at the "IF" function, though.
(I am assuming you have Ex2010.)

[nomadpat]

should be able to just do:

=if(E1 = "",B1,C1+D1)

[dukeofperl]

I have this working in 2007

=SUMIF(E1:E4,"",B1:B4)+SUMIF(E1:E4,"*",C1:C4)+SUMIF(E1:E4,"*",D1: D4)

however, there's something about it I don't trust ... maybe it's the wildcards (*)

possibly there is a way to wrap ISBLANK() and NOT(ISBLANK()) into it to make it more robust or possibly there is a totally different way.

I'll test it tomorrow when my eyes are fresh ...

Excel formulas sure are some funky stuff ... haven't worked this hard in a long time

Thanks to those that have posted a reply.

The space before D4 is intentional ... kept getting a smiley.

[cj]

I have this working in 2007

=SUMIF(E1:E4,"",B1:B4)+SUMIF(E1:E4,"*",C1:C4)+SUMIF(E1:E4,"*",D1: D4)

however, there's something about it I don't trust ... maybe it's the wildcards (*)

possibly there is a way to wrap ISBLANK() and NOT(ISBLANK()) into it to make it more robust or possibly there is a totally different way.

I'll test it tomorrow when my eyes are fresh ...

Excel formulas sure are some funky stuff ... haven't worked this hard in a long time

Thanks to those that have posted a reply.

The space before D4 is intentional ... kept getting a smiley.

What nomadpat posted will sum each row nicely, then just display the total in G1 as the sum of column F:

=SUM(F:F). You can hide column F if you don't want to see it.

http://www.pacefigures.com/images/excel.png

[dukeofperl]

What nomadpat posted will sum each row nicely, then just display the total in G1 as the sum of column F:

=SUM(F:F). You can hide column F if you don't want to see it.



Yes, nomadpat's formula works fine as does your addition to it ... thanks to both of you.

[jdhanover]

These guys beat me to the punch...post any other EXCEL issues here. happy to help

Jon

[dukeofperl]

These guys beat me to the punch...post any other EXCEL issues here. happy to help

Jon

Thanks Jon

Your offer is greatly appreciated.

[jdhanover]

No sweat. EXCEL I know well, picking horses that win, not quite as well...yet (working on that :D )

[HUSKER55]

you are using the wrong function key:D

[jdhanover]

you are using the wrong function key:D

Probably the key that popped off my keyboard, dang it! :D

[Tom]

There is a function that will return ******* for the number you input, but I cannot for the life of me remember what it is. The file I used it in got corrupted and I can't access it to find out.


6 ******
9 ********
3 ***

I used it to express speed figs.

[ThinkingAlways]

I would use a simple array formula to solve this:

=SUM(IF(E1:E4="",B1:B4,C1:C4+D1: D4))

Enter it as an array formula by ctrl+shift+enter

[dukeofperl]

I would use a simple array formula to solve this:

=SUM(IF(E1:E4="",B1:B4,C1:C4+D1: D4))

Enter it as an array formula by ctrl+shift+enter

Good tip -- thanks

[Zoot]

Tom this might be the function you're thinking of

In Excel, the Rept function returns a repeated text value a specified number of times.
The syntax for the Rept function is:
Rept( text, number )
text is the text value to repeat.
number is the number of times to repeat the text value.

i.e Rept("x",5) would return xxxxx

[Tom]

That's is!
Thanks, Zoot.

[BIG49010]

Does anyone happen to have a piece of code that would go to Equibase and check for any late changes or scratches and then flag you in Excel?

[dukeofperl]

I have another formula which I'm almost there on and need some advice.

See sample spreadsheet at:

http://myweb.cableone.net/skunkcreekcrew/sp2.bmp

Using the formula shown in the formula bar, it scans the red row until it finds the first cell value that is higher than the target number which is 141.5 in cell L3.

In this case, the target is 141.5 and it reports 160.8 which is the first cell value that is higher than the target number. That is precisely what I want.

Now that I know it will find the correct cell (finding the correct column is the important part) what I would like it to report is the date above the 160.8 which is 2/20 instead of the 160.8

I realize I will have to format the cell to display dates.

This is where I'm stuck and would appreciate any help.

Thanks

[Delta Cone]

You might be able to find a way to do that using the INDEX and MATCH functions... but I admit I don't have too much experience with them.

Read more here: http://www.ozgrid.com/Excel/index-match.htm

As you probably know, the HLOOKUP function will work, but the catch is that it needs to look up in the first row of data, while in your sheet the data is in the second row.

Here is a workaround for that:

Insert a row above the date row. In that row, simply type =B3, for example. Now the data in B3 (20.1) will also appear in B1. You can enter the data into row 3 as usual, and it will copy automatically to row 1. Simply hide row 1 and your sheet will look the same as it does now.

Now you can use the HLOOKUP to find what you want.

=HLOOKUP(L6,B1:K3,2,FALSE)

This formula says to look in the table B1:K3 and find the value you have in cell L6... then move down to the second row and give me what appears there (the date you are looking for).

In essence you are producing a duplicate of the data that appears below the date. But, you are putting this data in the top row so that the HLOOKUP function will be able to find it.

There are probably other ways to do this, but I find this way to be quite easy. Hope this helped.

[dukeofperl]

You might be able to find a way to do that using the INDEX and MATCH functions... but I admit I don't have too much experience with them.

Read more here: http://www.ozgrid.com/Excel/index-match.htm

As you probably know, the HLOOKUP function will work, but the catch is that it needs to look up in the first row of data, while in your sheet the data is in the second row.

Here is a workaround for that:

Insert a row above the date row. In that row, simply type =B3, for example. Now the data in B3 (20.1) will also appear in B1. You can enter the data into row 3 as usual, and it will copy automatically to row 1. Simply hide row 1 and your sheet will look the same as it does now.

Now you can use the HLOOKUP to find what you want.

=HLOOKUP(L6,B1:K3,2,FALSE)

This formula says to look in the table B1:K3 and find the value you have in cell L6... then move down to the second row and give me what appears there (the date you are looking for).

In essence you are producing a duplicate of the data that appears below the date. But, you are putting this data in the top row so that the HLOOKUP function will be able to find it.

There are probably other ways to do this, but I find this way to be quite easy. Hope this helped.

Thanks DC -- that works great.

I'm also going to mess around a bit with INDEX and MATCH.