• 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.

using sumproduct

J

jayalaxmi

Active Member
Hello All,

I am trying to find out a way to display status created dates according to status and Consumer request id i.e from col D to col Y. I had tried using sumproduct but I didn't get the correct output for it.Kindly help..Please write back for any clarifications.Attaching the sample for help.

Regards
 

Attachments

hey narayan,

thank you so much..It works absolutely fine...:awesome::cool::cool:

But How should I implement it in my main sheet..I am trying to do so..Coz the file you have worked is my dummy sheet..I am not getting the expected output when copy pasted the same in my main sheet.

Regards
 
Last edited:
Hi Jaya ,

I cannot say what the problem is ; have you entered the formula using CTRL SHIFT ENTER ?

Is the data layout in your working file identical to that in the sample file you uploaded ? Are all the columns which are used the same ?

Can you post details of where you pasted the formula in your working file , which cell address ?

Narayan
 
Yes narayan they are the same..I also tried acknowledging it with CSE. I also tried naming the columns with name manager the same like yours.

But it is not making sense yar.

=MIN(IF($A2=ConsumerServiceRequestIDs,IF(Sheet1!D$1=LogStatus,StatusCreatedDates))).
 
Last edited:
Hi Jaya ,

The following are all named ranges :

ConsumerServiceRequestIDs .... =Sheet2!$C$2:INDEX(Sheet2!$C:$C, COUNTA(Sheet2!$C:$C))

LogStatus .... =Sheet2!$J$2:INDEX(Sheet2!$J:$J, ROWS(ConsumerServiceRequestIDs)+1)

StatusCreatedDates .... =Sheet2!$K$2:INDEX(Sheet2!$K:$K, ROWS(ConsumerServiceRequestIDs) + 1)

You will need to either create these named ranges , or use the references instead of the named ranges , as in :

=MIN(IF($A2=Sheet2!$C$2:$C$12960,IF(Sheet1!D$1=Sheet2!$J$2:$J$12960,Sheet2!$K$2:$K$12960)))

Enter the above formula , using CTRL SHIFT ENTER.

Narayan
 
Or try to use non-CSE SUMIFS function

D2, formula copy across and down :

=IFERROR(1/(1/SUMIFS(Sheet2!$K:$K,Sheet2!$C:$C,$A2,Sheet2!$J:$J,D$1)),"")
Regards
Bosco
 

Attachments

Hey I tried my best..but its not working in my original sheet. Bosco yours is also working in the dummy file..I m trying to attach my original sheet but it is so heavy file that the zip file is also not getting attached. Can i get your personal Skype id or something so that I can post the file there..

Regards
 
NARAYANK991 said:
Hi Jaya ,

The following are all named ranges :

ConsumerServiceRequestIDs .... =Sheet2!$C$2:INDEX(Sheet2!$C:$C, COUNTA(Sheet2!$C:$C))

LogStatus .... =Sheet2!$J$2:INDEX(Sheet2!$J:$J, ROWS(ConsumerServiceRequestIDs)+1)

StatusCreatedDates .... =Sheet2!$K$2:INDEX(Sheet2!$K:$K, ROWS(ConsumerServiceRequestIDs) + 1)

You will need to either create these named ranges , or use the references instead of the named ranges , as in :

=MIN(IF($A2=Sheet2!$C$2:$C$12960,IF(Sheet1!D$1=Sheet2!$J$2:$J$12960,Sheet2!$K$2:$K$12960)))

Enter the above formula , using CTRL SHIFT ENTER.

Narayan
Click to expand...
I tried replacing the contents with MIN(IF($A2=Sheet1!$C$2:$C$12960,IF(Sheet1!D$1=Sheet1!$V$2:$V$12960,Sheet1!$W$2:$W$12960))) this.
 
Hi Jaya ,

=MIN(IF($A2=Sheet2!$C$2:$C$12960,IF(Sheet1!D$1=Sheet2!$J$2:$J$12960,Sheet2!$K$2:$K$12960)))

Can you check the tab references ?

Narayan
 
You must log in or register to reply here.
Back
Top