HELP on Formula

[Guest]

Hi Folks,

Here is the situation. I have a range of cells with drug names in them what
I need is when a date is input into a pecified field I need this to total up
in a different cell but this has to be done by Months. See below.

A1 B1 C1
Drug name Aspirin Paracetamol
45M 36F
02/02/06 03/03/06

Result would be as follows:

Totals Aspirin Paracetamol
Jan 0 0
Feb 1 0

Mar 0 1
Apr
May
etc

There will be other Data entered into columns which contain the date e.g.
45M etc age and sex of person so the formula can only recognise the date part
and add the totals.

Can anyone help???


Thanks

 

[Guest]

=IF(ISBLANK(B3),"",SUM(B8:H8))

Try this, I'm not sure where you need it toread the total, or what rows ( or
column to sum up
but this is basically looking at B3 as the cell which would have the date in
it, then if the cell is blank, it will also be left blank, but if the cell
has an entry then it will sum up row 8 column B through H...
you will need to edit it as you see fit.

 

[Guest]

Hi this isn't returning the data I require, the column with the drug name is
long to acommodate a few entries along with other data like age and sex,
incident number etc, the formula must only recognise date entries from this
column and total in a summary sheet as per month as below updated.

Thanks

Mark

 

[Sandy]

Here we go this might take some lines to explain, but here it goes:

A B C

1 Drug Name Aspirin Paracetamol
2 45M 36F
3 02/02/06 03/03/06
4
5 Totals Aspirin Paracetamol
6 1 15 23
7 2 4 12
8 3 21 5
9 4 2 17

Enter into a new spreadsheet(for testing purposes) the above
Place this formula in cell B4
=IF(ISBLANK(B4),"-",VLOOKUP(MONTH(B4),$A$5:$C$9,2))
Place this formula into cell C4
=IF(ISBLANK(C4),"-",VLOOKUP(MONTH(C4),$A$8:$C$9,3))

The reason I have changed the dates in A6 to A9 is that the formula is
extracting the month serial (1 for Jan, 2 for Feb, etc.) from your date
and using that value for the lookup value in the vlookup function. If
you are confused at all about the formula here's a quick bearkdown of
what it does...

For the formula in the aspirin column it is stating If the date
cell(B4) is blank then display a hyphen ("-"), if it isn't blank then
vertical lookup in range A5 to C9 the month value of the date in B4 (in
the case of above "2") find that row and return the value in the column
#2 in the range (Column B). That's pretty much all it does if you need
vlookup explained more look in help and in the posts in
microsoft.public.excel.worksheetfunctions.


Hope this is what you were looking for!


Sandy

 

[Guest]

Tried this and its not doing as I require, Data for example in a column for
Aspirin

Aspirin
123456 - incident No.
45M - age & sex
02/06/06 - Date
POS - Positive outcome

123456
56F
03/07/06
POS

123456
87M
25/09/06
POS

I need the above to return data into cells
Aspirin
Jan
Feb
March
Apr
May
Jun 1
Jul 1
Aug
Sep 1
and so on

This sums up the amount of aspirins administered in months June & July &
September and so on, must calculate totals as per month given


Any Help.


Thanks


Mark