Multiple Formulas

[Craig]

Hi...Very much learning!!

I have a spreadsheet that needs three parts sorted - xl 2003

Q!: In column A I have a location say Exeter....in column D I have a type of
Benefit say JSA - how can i write a formula so that on Sheet 2, I can count
how many customers in Exeter are claiming JSA based on where they are,
according to the benefit claimed....i suspect COUNTIF, but dont know how to
link it all together.

Q2: In column E, I have a date a referral was made....I want to use a
Green, Amber, Red, CF method, so that if date entered is up to 7 days from
the date entered (not TODAY as it wont always be that) the cell goes green -
I am using: =IF(E3<>"",E3>TODAY()-7), for Amber: =AND(E3<>"",E3>TODAY()-14)
and Red: =AND(E3<>"",E3>TODAY()-600)...the date needs to be updating so will
go from Green to Amber to Red as the CF applie - I know the formulas are
wrong but bin searching dicussion groups/net forr ages.

Q3: I would like to further develop this CF so that the colour can be
entered into column F as text i.e. Green, Amber or Red so that I can use a
simple COUNTIF, which I can do from that column.

Apologies for this long thread....tried asking the 'experts' in work, but
alas, their knowledge it would seem is best kept for them, just got 'do this'
and am now floundering !!! any help greatly appreciated and will add to my
kowledge so IT section can give the normal response....switch off / switch
off...sorry peeved with them

Thanks

Craig

 

[Pete_UK]

Q1 - COUNTIF (and SUMIF) is used where you have only one condition.
You have 2, so the way to do this is:

=SUMPRODUCT((Sheet1!A1:A100="Exeter")*(Sheet1!D1100="JSA"))

Better to put those variables in cells, eg put Exeter in A2 of Sheet2
and JSA in B1, then put this in B2:

=SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$D$1:$D$100=B$1))

Adjust the ranges to suit how many rows of data you have. Then you
could have other benefits listed across row 1 and other towns in
column A and copy the formula across and down to suit.

I have to go for now (Corrie), but I'll come back later to address Q2
and Q3 if no-one else has.

Hope this helps.

Pete

 

[Pete_UK]

Q2 - I don't know what you mean by:

"...so that if date entered is up to 7 days from the date entered ..."

Do you mean that you intend to put some reference date in another cell
and you want to compare with that? If so, which cell are you using for
the reference date?

Q3 - Same comments apply as above. Once we sort out what you are
doing, it's quite easy to have a multiple IF in column F which will
return the appropriate colour.

Pete

 

[Craig]

Pete...Thanx for first part.....works brill.

Now, 2nd part: written this 3 times so far to try to make sense...here goes:
my referral date could be in the past or today, as the idea is to show where
we are running at Red, Amber or Green from the date entered.

It should work as: if the date entered is up to 7 days old, we are
green....if the date is between 8 days and 14 days old we are Amber...if over
15 days Red....i suspect the answer to your question about reference date is
it would be TODAY

The Green, Amber and Red method should be updating so that as the days go
past the 7...8-14 & 15+, the cell in column G returns a TEXT value stating
the colour....i can then use a COUNTIF on sheet 2 which is collating my
stats,
taking the colour as written in text from sheet 1.

I dont think i need to count colours, just get a formula to work out that if
the date refers to a colour, the ccolour can be put into text......tried
modcolour functions etc and deffo having probs with them

I believe, Q2 will change my thinking and subsequent layout.....so Q3 will
be different if Q2 is workable

Long thread again....hope that clears it a little more