Is there a better way of writing this Sumproduct between range formula?

**sans** · 11-07-2011, 08:44 AM

Hello,

I am just wondering if this formula I've done could have been written in a different or better way.

=SUMPRODUCT(--((A1>=0)*(A1<=4))+SUMPRODUCT(--((A2>=0)*(A2<=4))+SUMPRODUCT(--((A3>=0)*(A3<=4))+SUMPRODUCT(--((A4>=5)*(A4<=9))+SUMPRODUCT(--((A5>=5)*(A4<=9))+SUMPRODUCT(--((A6>=5)*(A6<=9))+SUMPRODUCT(--((A7>=5)*(A4<=9)))))))))

Thank you
Sans

**NBVC** · 11-07-2011, 08:48 AM

You probably don't need the SUMPRODUCTs wrapping the formulas...

i.e. (A1>=0)*(A1<=4)+(A2>=0)*(A2<=4)+(A3>=0)*(A3<=4), etc....

**sans** · 11-07-2011, 08:55 AM

Thank you. Is it also possible to achieve the same result with a countif formula?

**NBVC** · 11-07-2011, 09:11 AM

I don't think so since your comparisons are not uniform.

for example if you were checking that range A1:A7 was between 0 and 4, then you could use COUNTIFS.

e.g.

=COUNTIFS(A1:A7,">=0",A1:A7,"<=4")

**sans** · 11-07-2011, 09:26 AM

I understand, thank you for the explanation. I am just trying to learn a little more each day. I am just wondering if there is anything excel can't do

**sans** · 11-07-2011, 10:18 AM

If the following values

9 4 9 2 1 7 10

are in the corresponding cells in the formula below, shouldn't the result be 3? The result I am getting is 0.

=SUMPRODUCT((LS4>=5)*(LS4<=9))+((LS5>=5)*(LS5<=9))+((A3>=5)*(A3<=9))+((A4>=5)*(A4<=9))+((A5>=5)*(A4<=9))+((A6>=5)*(A6<=9))+((A7>=5)*(A4<=9))

Sans

**NBVC** · 11-07-2011, 10:26 AM

I get 4..

Also, you don't need SUMPRODUCT at all...

i.e. your formula can be just....

=(LS4>=5)*(LS4<=9)+(LS5>=5)*(LS5<=9)+(A3>=5)*(A3<=9)+(A4>=5)*(A4<=9)+(A5>=5)*(A4<9)+(A6>=5)*(A6<=9)+(A7>=5)*(A4<=9)

It evaluates each condition and returns TRUE or FALSE.. then TRUE*TRUE gives 1 and all other combos give 0... each of these resultant 1's and/or 0's are then added together to give final result.

**sans** · 11-07-2011, 10:56 AM

Shouldn't the result be 3 in the case of 9 4 9 2 1 7 10 as only 3 numbers are between 5 and 9?

I am getting 3 now if I use the formula in a new sheet but if I use it in the normal sheet I keep getting 0. Does it make any difference if the values 9 4 9 2 1 7 10 have formulas in them?

Thanks
Sans

**NBVC** · 11-07-2011, 11:14 AM

The combination of Conditions that give TRUE*TRUE are:

(LS4>=5)*(LS4<=9)
(A3>=5)*(A3<=9)
(A6>=5)*(A6<=9)
(A7>=5)*(A4<=9)

assuming that L24:L25 is 9 4 respectively and that A3:A7 contain 9 2 1 7 10 respectively

As long as the formula results are actually numbers (not text that look like numbers) then it should work.

**sans** · 11-07-2011, 11:26 AM

I understand the conditions of the formula but I keep getting 0 for some reason. I am attaching the example. In the normal workbook the cells are not in a range so I cannot use the COUNTIFS formula you have written above

Thank you
Sans

**NBVC** · 11-07-2011, 11:55 AM

The cells that have a green triangle in them are formatted as text... click the little popup icon (exclamation point in yellow triangle) and choose to convert to number

**sans** · 11-07-2011, 12:08 PM

Oops, the background error checking option was disabled in the Options section. Thanks NBVC

**sans** · 11-07-2011, 12:12 PM

I just saw that the error checking option was enabled in the original workbook but not in the example attached above.Now again I keep getting 0. It's probably something silly.

**NBVC** · 11-07-2011, 12:48 PM

Enter a 0 in any cell and copy it. Then select A1:G1 and right-click, pick Paste Special and select Add. This will convert the cells to Numbers, then go to Format and select General in the Number tab to ensure the format remains as General.

**sans** · 11-07-2011, 01:37 PM

Ok, I finally found the problem, but I don't know how to fix it. The numbers 9 4 9 2 1 7 10 were obtained by using the formula =RIGHT(LS4,1),....
As soon as I deleted the formulas in those cells and simply wrote the numbers 9 4 9 2 1 7 10 the

=(LS4>=5)*(LS4<=9)+(LS5>=5)*(LS5<=9)+(A3>=5)*(A3<=9)+(A4>=5)*(A4<=9)+(A5>=5)*(A4<9)+(A6>=5)*(A6<=9)+ (A7>=5)*(A4<=9)

formula worked. Is there a way that this can be circumvented?

Thanks
Sans

**NBVC** · 11-07-2011, 01:59 PM

Use instead:

=--RIGHT(LS4,1)

the -- coerces the text string (returned from RIGHT function) to number.

**sans** · 11-07-2011, 02:28 PM

That did the trick! Thank you!!!

Is there a better way of writing this Sumproduct between range formula?

LinkBack

Thread Tools

Rate This Thread

Display

Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Re: Is there a better way of writing this Sumproduct between range formula?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions