Need help with sumproduct function

[kishoremcp]

Hi all,
I am using office 2010. I am calculating some data using the formula

=sumproduct(countifs(A:A,"Phone",J:J,"US",K:K,'Rough'!A1:A5))

But it is taking long time to calculate. Is there any other way to calculate..Pleaes help....

Tha Overall data range is A1 to AE10000.

[dilipandey]

Hi kishoremcp,

What is there in Rough sheet A1:A5 which you are looking in K column....
Also, do you need to search entire columns like A:A, J:J etc or it would be A1:A500 , J1:J500


Regards,
DILIPandey

<click on below 'star' if this helps>

[kishoremcp]

I have a list of names which are some of the list in the column L. I am sorry, It is not K:K it is L:L.

It is not necessary to search for entire A:A, it can be up to 8000 only. One more thing..Is it possible to make this as a macro. I am attaching the file for reference...

I need like in sheet date I want to have the count of 1's which are for the agents list which are in the sheet Rough.
Like the same way I am also looking for 2's in the correcponding row date wise and in sheet Agent I want for agents.

The conditions are the column A in raw data should be US&C, F should be Phone, P should be 1's for 1's in sheet date like that....

Please help me if we can prepare a macro for this ...
Thanks in advance....

[kishoremcp]

any help!!

[shambhu24]

I would like to use sumproduct function with effect of dropdown list. effect of drop down list will be posetive and negative for reasult value and some range. is this possible from sumproduct function .? or i will have to use another function and which one is appropriate and how to use this?? please siggest me

[zbor]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Marcol]

@ kishoremcp

See if this workbook helps, it uses Dynamic Named Ranges, so the SUMPRODUCT() formulae always calculate the exact ranges.
Add or subtract from your data list in Sheet "Raw Data", the ranges will adjust according to the last entry in Column L "Agent"

In Sheet "Agent" B2

Please Login or Register  to view this content.

Drag Across to Column H, Then Down
$K$2:$L$2 contains dropdowns, select as required.

[kishoremcp]

Hi Marcol, can you also help me in validating the month also (Example: For the month of May) like that.....

[Marcol]

Please don't PM members with further questions see Forum Rules Rule #4

Here is the relevant content of your PM

.... But it should calculate 1's only from the column P from raw data sheet but not all the 1's.
At the same time please help me to calculate date wise also in sheet 1 for those agents only. I mean on may 1 for all the agents listed in Rough Sheet it should calculate all the 1's, 2's ....etc...

I'm a bit confused as to what you really want ...
Your last post states

... can you also help me in validating the month also (Example: For the month of May) like that.....

Was the sample submitted okay, or does it need amended as per your PM ...

See this workbook

[Marcol]

This workbook is amended as per your PM, I think ... still a bit ...

[kishoremcp]

Thanks Marcol... You are really great... I had only one question left. Where did you mentioned in that file to capture the data from sheet Raw dAta and at the same time, could you also help me in date wise data like the same in sheet date.. But here I want for only the agents who are mentioned in the sheet Rough...

Thanks again really . you are really helping me a lot and at the same time you are helping me to follow the rules whenever necessary...

[Marcol]

1/. Look at the Names Manager, the named ranges refer to Sheet "Raw Data" i.e. 'Raw Data'!
They are dynamic and therefore adjust to allow for your sheet growing/shrinking.
The controlling column is 'Raw Data'!$L:$L "Agents".

I can't see why you need Sheet "Dates", the formula calls complete months
If you need to find a specific date range say 5 April to 10 May, then substitute in B2

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Then Drag Across and Down.

You could of course put these dates into M2:N2, data validation should then be Date > From ??/??/2005 to ??/??/2012. (to suit the First date in your workbook to last expected date)

[kishoremcp]

Yes, i understand what you say and the thing is, if i add the date also as you mentioned there i will get only for 1 pirticular day but not for the entire date range. I need it because i need to plot a graph according to the data which is from date range. (The senario is : I have the list of agents listed in sheet rough. I will have another set of agents for another lead ) so i have to get the date wise data also for the agents listed in rough sheet. I hope I am clear and u understand my requirement)

[kishoremcp]

Hi Marcol, I dont know where I went wrong with the thing. I did just like what you did in the previous attachment to my new file. It is not working for me. Attaching the file. Please correct it and if possible please help me in understanding

Please Login or Register  to view this content.

Also attaching my new file for which the output is not coming...

[Marcol]

1/. Your sheet "Raw Data" column "Case Opened" does't have dates!
You have somehow managed to convert this column to a string.

In a spare sheet put this in A2

Please Login or Register  to view this content.

Drag down to Row 1019
Copy the result and Paste Special > Values to sheet "Raw Data" P2 ("Case Opened")
Format the column
Format Cells > Number Format > Custom
Type:=

Please Login or Register  to view this content.

2/. Your formula in Sheet2 B2 has an extra anchor, it should be

Please Login or Register  to view this content.

3/. If you don't need this to work with 2003 and earlier, remember you can use COUNTIFS() instead of SUMPRODUCT()

See this workbook

[kishoremcp]

Hi Marcol,
Thank you for your help really.
I have tried to do it with countifs but it is not giving me the result. Please give the formula for one cell with countifs with start date and end date.

Ur formula given is

Please Login or Register  to view this content.

please provide the sameformula in countifs so that it can calculate bit faster...

Thanks...

[kishoremcp]

Hi Marcol, I need ur help....please go through the above one....

[Marcol]

I don't have 2007 or above at the moment, so I can't check this

Please Login or Register  to view this content.