Multiple IF statments

[garethgtt]

Hi Guys,

Ok ive been tearing my hair out over this...
Ive been asked to show how many unsold houses we have that are at the RT stage...

I think i need 2 IF statments combining into one calculation.

Cells U40 to AX40 need to display the amount of unsold houses we have, by going off the dates in the researved column B9-B24, once its reserved its classed as sold, anything prior to that date or a blank cell means its unsold.

once that is worked out,those results need to be narrowed down to only count the houses for those dates that have RT in the table prior to that date.


You guys have been a great help in the past so im hoping you can help me with this one

[NBVC]

Try:

=SUMPRODUCT( ($B$9:$B$24="")+($B$9:$B$24>0)*($B$9:$B$24<U$36),--(U$9:U$24="RT"))

copied across

[garethgtt]

Thanks, but that doesnt work, maybe i didnt explain it properly.
What ive done is stripped the origonal spreadsheet right down and entered manually the correct data to demonstrate the outcome the formula needs to produce

Notice how the house reaches RT on 20/07/07 which is counted and then the 27th and the 03 are counted because the house has already reached RT (even though those squares are blank)then on the 10th (which i coloured) the Reservation date kicks in which means the house is now sold.

does that make sense?

Thanks

[rylo]

Hi

Can you update your original file and in U39:AX39 put in the number you would expect to return in the cell below (U40:AX40).

IS this to be for the 07/08 financial year only? If so, then can the items in row 9 be rejigged? YOu have a RT date that is later than the reservation date. Or explain how this would have to be handled.


rylo

[garethgtt]

Ok ive stripped almost all the data out to make it easier and ive placed the results i expect in the cells above - U39:AX39

Ultimately the dates will span upto 5 years. also notice the Week ending dates are just formatted as DD and are correct dates underneath.

[rylo]

Hi

Sorry, didn't express that well enough.

Can you refill some more data so that there are multiple rows with items. Getting the result for a single row isn't all that hard. It is when there are multiple items that have to be considered that things get interesting.

The original data wasn't too bad. I was just concerned about row 9, the date from the previous year, and would like to have know what output was correct for comparison purposes.


rylo

[garethgtt]

Hi

Sorry, didn't express that well enough.

Can you refill some more data so that there are multiple rows with items. Getting the result for a single row isn't all that hard. It is when there are multiple items that have to be considered that things get interesting.

The original data wasn't too bad. I was just concerned about row 9, the date from the previous year, and would like to have know what output was correct for comparison purposes.


rylo

Ive added a couple more lines of data and added the results, row 9 reservation date is before the first RT of that line so that wouldnt count anyway.

I still cannot figure out how to do this.. HELP......

[rylo]

Hi

Getting more confused by the day.

W39: 1 - OK, get that
X39: 1 - don't get that. Why is there another RT in X10 and why doesn't that make the count 2.
Y39: 1 - again why not 2.
Z39: 0 - ??? I can understand the reduction due to the Reservation date, but as a follow on from above, why not 1???
AA39: 1 - Even if the progressive to Z39 is 0, why isn't this 2? There are entries in AA9 and AA12??
.....
Can you put in an explanation of why/how each number has been calculated.


rylo

[garethgtt]

X39 - There is another one in X10 because on that particular occasion for that house it took 2 weeks to be rooftiled (RT) so there are 2 RT`s but its only counted once because once its hit RT status its like binary, its either on of off --once its on its counted until it reaches its reservation date, no matter how many RTs are on that line or wether they are blanks or filled with anything else.

Y39 same as above

To help you understand whats happening and what im trying to do.
RT is just short for Roof Tiled and our accountant wants to know how many houses we have that are unsold (reserved means its sold) at RT status, somtimes RT takes more than 1 week hence the multiple RT`s for the same house but each house is only rooftiled once, hence it being counted only once, you cant roof tile a house twice.

hope that clears things up?