Countif

Riki

New Member
Joined
Mar 22, 2002
Messages
2
I have a list of rainfall data, ie

1.4
0.6
*0.2

How do I sum these three lines to get 2.2?
An * would normally only preceed the odd small number 0.8 and below.

Tks
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
On 2002-03-23 23:07, Riki wrote:
I have a list of rainfall data, ie

1.4
0.6
*0.2

How do I sum these three lines to get 2.2?
An * would normally only preceed the odd small number 0.8 and below.

Tks

Riki,

Array-enter:

=SUM(IF(LEFT(A1:A3)="*",MID(A1:A3,2,LEN(A1:A3))+0,(A1:A3)+0))

where A1:A3 houses the values of interest.

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

Aladin
 
Upvote 0
Hi Rick


It is not godd practice to mix text and numbers when you really need a number. Array formulas are also a step in the wrong direction.

Just Custom Format your cell(s) that you want to show a * with the number as:
"*"0.0

This way you will see *0.2 but its true value will be 0.2 and you can use a normal SUM function.



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-24 00:35
 
Upvote 0
Eagerness to "wage war" makes one sloppy.

It would have been a good advice to posit:

Custom format the column in which you enter the rainfall readings as

[<=0.8]"*"0.0;General

and you will be able to use the ordinary SUM worksheet function.

But, not as custom format cell by cell, abusing user time:

"*"0.0

or the whole column unless one wants the whole column to show stars preceeding all numbers.

A side note. Why didn't I suggest this in my first reply? The rule I follow if it matters to know: Accept the user's routines (in tis case) of data entry if that does not conflict with the proccessing requirements. The cost of a single cell array-formula to sum starred and unstarred entries is definitely a "quantitée négligeable" set off against breaking a user routine. This consideration shows up clearly when users insist of entering (start and end) times as 2245, 0300, etc. instead of 22:45, 03:00, etc.

Aladin
This message was edited by Aladin Akyurek on 2002-03-24 01:40
 
Upvote 0
>Eagerness to "wage war" makes one sloppy

I'm very sorry you feel that way Aladin, but That's really your problem not mine.


No big deal applying a custom format when you have the "Format painter"


While one or only a few arrays will have no real adverse effect it really is very bad advise. You will certainly not realise it Aladin but I have been sent many an unusable spreadsheet which are due to poor advise about using arrays. When I ask why they allowed this to happen, they all say the same "I didn't realise!".

Aladin, my idea of helping is not a 'glossy cover' approach, which is all arrays are in most cases.

RE: Why didn't I suggest this in my first reply?
You, I and most others reading this thread can patently see this reason. :eek:)
 
Upvote 0
Hi Aladin and Dave:
I feel fortunate that I am able to participate in this forum -- different approaches, personal preferences, pros & cons of different approaches, pitfalls, and new discoveries and all ...
So please keep enligtening us with different viewpoints withoust being defensive about it because if you will hold back your viewpoint you would deny us viewers the benefit of your invaluable insights and experiences...
With GREETINGS and REGARDS!
 
Upvote 0
On 2002-03-23 23:07, Riki wrote:
I have a list of rainfall data, ie

1.4
0.6
*0.2

How do I sum these three lines to get 2.2?
An * would normally only preceed the odd small number 0.8 and below.

Tks

Hi Rikki:
Aladin and Dave have already posted valuable approaches to your inquiry...
I have a question for you -- do I understand it right that rainfall under 0.8" will be preceded with an * (I realize you do have a vague qualifier in your writing) -- the reason I mention this is that Aladin gave you a formula that will flag all rainfall entries below 0.8" with an *, whereas Dave gave you an option to format entries selectively to be marked with *.

If you go with Aladin's suggested approach, your entries will be displayed as

1.4
*0.6
*0.2
Total rainfall 2.2

Why was 0.6 entry in your post not get preceded with an * ... JUST CURIOUS!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top