Understanding SUMPRODUCT in my formula

IF(SUMPRODUCT(--($B$23:$B$66>=$B$16),--($B$23:$B$66<=$B$17),--($D$23:$D$66=0))>0,0,.......

Looks in range $B$23:$B$66 for values >= from b16 value.
Looks in range $B$23:$B$66 for values <= from b17 value.
Looks in range $d$23:$d$66 for values = 0

If formula finds even 1 result(>0), then the result will be 0, or else the result will be the second SUMPRODUVT.

This explanation of SUMPRODUCT is worth reading.

Thanks Palmetto

I do so hate just relying on a forum without trying to seek the answer myself.

Hence, I have googled about 146 websites over 2 hours this morning and about 100 websites over the entire afternoon yesterday but to no avail.

I did find the website you suggest but nowhere (as far as I can see) does it explain the individual components of a formula like mine...unless you can point out where it does ?

Thanks

Thanks Fotis1991, much obliged.

I'm wanting to change the formula so that if it finds:

even one result greater than zero (ie it could be a mixture of 0's, 1's, 2's or 3's) it performs the "second SUMPRODUCT" otherwise (ie all values are 0), it returns a value of 0.

I've tried the following formula but it's not working. Can you help please ?

=IF(SUMPRODUCT(--($B$23:$B$66>=$B$16),--($B$23:$B$66<=$B$17),--($D$23:$D$66=0))>0,SUMPRODUCT($D$23:$D$66,--($B$23:$B$66>=$B$16),--($B$23:$B$66<$B$17)),0)

Thanks in advance

Can you explain the circumstances in which that doesn't work? I assume you get the wrong result rather than an error?

What's in the range $D$23:$D$66 just positive numbers or zeroes?

Can you upload a small sample workbook?

Originally Posted by bellevue

I'm wanting to change the formula so that if it finds:

even one result greater than zero (ie it could be a mixture of 0's, 1's, 2's or 3's) it performs the "second SUMPRODUCT" otherwise (ie all values are 0), it returns a value of 0.
Thanks in advance

Hi,

Have you considered simplifying your approach with a helper cell. If part of your determination is whether a range of cells has a value > 0 then put an =SUM(your_range) in the helper cell, and then make the helper cell the subject of an IF test in your main formula. i.e.

=IF(helper_cell=0,0,your_SUMPRODUCT_formula)

Originally Posted by bellevue

What I'm waiting to do is create a formula where even it finds one cell (within the range) greater than zero (ie it could be a mixture of 0's, 1's, 2's or 3's in the range) it performs the "second SUMPRODUCT" otherwise (ie all values are 0), it returns a value of 0.

Within which range, $D$23:$D$66? If so try

=IF(COUNTIF($D$23:$D$66,">0"),SUMPRODUCT($D$23:$D$66,--($B$23:$B$66>=$B$16),--($B$23:$B$66<$B$17)),0)

...although if that was correct then the "second SUMPRODUCT" on its own would surely give the same result, i.e.

=SUMPRODUCT($D$23:$D$66,--($B$23:$B$66>=$B$16),--($B$23:$B$66<$B$17))

...What I'm waiting to do is create a formula where even it finds one cell (within the range) greater than zero (ie it could be a mixture of 0's, 1's, 2's or 3's in the range) it performs the "second SUMPRODUCT" otherwise (ie all values are 0), it returns a value of 0.

Not sure that i understand correct your issue, but: A simple SUMPRODUCT, doesn't do this?

=SUMPRODUCT((B23:B66>=B16)*(B23:B66<=B17)*(D23:D66))

Originally Posted by bellevue

Thanks Richard


If I go with a helper cell (god knows I need help !) where would I put that within the original formula of...

=IF(SUMPRODUCT(--($B$23:$B$66>=$B$16),--($B$23:$B$66<=$B$17),--($D$23:$D$66=0))>0,SUMPRODUCT($D$23:$D$66,--($B$23:$B$66>=$B$16),--($B$23:$B$66<$B$17)),0)

Thanks

Hi,

Try

Formula:

Please Login or Register  to view this content.

Thanks all, I think I may need to add some more explanation.

Firstly, the spreadsheet I'm using is quite large and contains sensitive information; hence, when asked in the original post (http://www.excelforum.com/excel-form...66#post2999966) to upload a sample worksheet, I had to create a sample. The problem with this is that cell references in the original and the sample don't match so it's getting quite complicated inserting suggestions into it.

So, with that in mind, I've created a new sample worksheet which is closer to the original (layout wise) and included it with this posting.

I'll also include a description of how it works, sorry that it's lengthy but there's no other way.

The spreadsheet is used to display the availability and prices of a range of holiday properties for either stays of 7 nights or more (row 23) or for short breaks (row 24); for this sample worksheet, I've listed just one property and will refer only to stays in multiples of 7 nights.

The dates for each enquiry are entered in cells Price List!D1 and Price List!D2 and the prices for the stay are shown in Price List!D23.

There is a worksheet ("Avail" tab) where the availability of the property is recorded, using numbers, as follows:

0 (or a blank cell) = the property is available
1 = we have a booking
2 = the owner has a booking
3 = the property is not available

The total price for the selected dates is calculated in Wkly!D18:D23; Wkly!D23 identifies (from "Avail") whether a property is available or not.

The formula in that cell, currently (I think), checks for any zeros in the selected range of dates and returns a zero if any are found; otherwise, it performs a calculation which is, basically, a negative figure equivalent to the total of cells Wkly!D16:D22.

However, this means that some of the dates (in Avail!) for a range of dates could be 0’s (available) and some could be 1, 2 or 3’s (not available) but, for example, a stay from 15/06/13 to 29/06/13 would return a price even though the property isn’t available from 22/06/13 to 29/06/13.

The Question

In simplicity, I need it to check for any number greater than 0 in the range of cells and, if there are any, return the result to be a negative figure equivalent to the total of cells Wkly!D16:D22; if all of the cells show a 0, then it returns a 0.

One final note, there is a 5% discount given for stays of 14 days or more, just to explain the odd price shown in Price List!D23 for stays of 14 days of more.

OK, I hope you are still with me and I also hope that someone can help !

Thanks in advance.

PRICELISTExcelForum230113.zip