Excel Calculations on Calculations??? 1

[mcaoneill]

Hey,

I am thinking this might be impossible, but I will ask.

I have a spreadsheet (they do not want a database).For tracking patients in a clinical trial.

I have thus far (with help from some brillant people here) set it up as follows:

A B C D E F G H
Pt # PT IT Date Act Date $ date Act Date $

This goes on for 8 Cycles of treatment with up to 10 patients partipating, I have Column F calculate 21 days after the date that is entered into B, I calculates off of F. When a date is entered into the "Date" column the dollar amount is filled in.

Now for the real info I need calcuate how much money is spent by quarter for all patients this would be based off of how many dates are actually entered into each date column. The Act date column is for our purposes not finance.

I can get Excel to COUNT IF how many dates are within a quarter, but I CANT NO MATTER HOW HARD I HAVE TRIED, figure out how to make it calculate colum E based off of dates in Column B and so forth.

Thank you for reading this and thank you even more for trying to help.

Maureen

 

[GlennUK]

You need to use SUMPRODUCT with some logical comparisons. Here is an example this might be close to what you want:

=SUMPRODUCT((B2:B26>=DATEVALUE("1/3/2004"))*(B2:B26=<DATEVALUE("30/5/2004"))*E2:E26)

This formula sums the contents of E for dates in column B that are between 1st March and 30th May. ( your date system might be different ).

You could replace the DATEVALUE functions with references to cells containing the start and end dates you need.

Hope that helps.
Cheers, Glenn.

 

[mcaoneill]

Glenn,

Thank you so much, it works perfectly.

Best wishes
Maureen

 

[GlennUK]

My pleasure

Cheers, Glenn.