• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT not working correctly.....

desert rat

desert rat

New Member
I am having problems with the below SUMPRODUCT returning an incorrect total.


=SUMPRODUCT(--($U$2:$U$236>=140)*($V$2:$V$236>=90),SUBTOTAL(103,OFFSET(U2,ROW(U2:V236)-ROW(U2),0)))


It should return a total of 42 but is only giving a result of 22.


Can anyone see a problems with this?
 
Hi ,


I don't know if anyone can answer this question , since it involves an error in calculation , which will not be apparent from the formula ; we need to look at the data in order to see where the problem lies.


However , the way the formula has been written , is this what you are looking to do ?


1. Look at those values in column U ( in the range U2:U236 ) which are greater than or equal to 140.


2. Look at those values in column V ( in the range V2:V236 ) which are greater than or equal to 90.


3. AND the above two conditions.


4. Now look at the range U2:U236 and take a count of those cells which have data , ignoring hidden rows , for those rows which meet the criterion in (3) above.


How have you decided that the total should be 42 ? Through an AUTOFILTER ?


Can you give more details ?


Narayan
 
Desert Rat


Posting a sample file is the easiest way for us to assist you with this style of problem


Refer the Green Sticky post
 
Thansk for the feedack.


the formula is meant to look at the values in column (U2:U236)and (V2:V236) and if the data in column 'U' on one particulr ro is greater than or equal to 140, and the data on the same row in colum 'V' is greater than or equal to 90 then it will add it to the total.


Here is a link to a smaple set of data https://rapidshare.com/files/2264300468/Sample.xlsx


it's pretty basic data but if you need more info let me know


Thanks
 
Hi ,


I tried out the formula you posted initially , and it gives the correct results ; first it gave 5 ; then when I hid some of the rows , it reduced the count correctly.


I suggest you recheck your data.


Narayan
 
Finally got this one to work as well! Had some of the subtotals linking to wrong the cells.


Now the fun stuff begins - trying to get the spreadsheet to use the right data to create the graphs! :)


Thanks a million for all your help Narayan
 
You must log in or register to reply here.
Back
Top