Hi All,
I am trying to calculate the value from a table based on multiple rows and columns. the attached file has the data in the 'Closed' tab. The criteria is as follows:
1) Closed column A = SEAU OS A1
2) Closed column B = In Home
3) Closed column C = TAT
4) Closed column D = Total
5) Closed row 1 = SEAU OS B1
as an example, taking INFINET for A1 and week 30 (AE1), the returned value should be 13.5
I have tried various iterations of SUMIFS, SUMPRODUCT and no luck.
please help!!!
cheers
Hi and welcome to the forum
I am unble to open your file, it says it me be corrupt. Can you try to upload again please?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
hopefully now it works
cheers
Still doesn't workOriginally Posted by Fade-e
hopefully now it works
cheers
Nope same problem...
...excel cannot open the file KPI-CTAT-test.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file...
im using 2010 if that makes a difference?
it opens for me when I download it :S
cheers
im on 2007 but that should not make a difference
im at work at the moment so don't even have access to dropbox or other file sharing site
not all members can access those kind of file hosting sites (company firewalls and stuff). And anyway, I doubt its the upload process that is the problem, we upload dozens of files daily here.
Is there any way you can put together a dummy file based on your data?
one last try otherwise will have to wait till I get home.
thanks for trying this far
cheers
see if this works
the 3rd try of your file still did not work, but the book1 file did
Based on the data in your dummy file, and the the criteria you set (and I know you want them cumulative, but this is to illustrate), these are the cou8nts of the individual criteria. So if you want those criteria compounded, you will always get zero as an answer because there is nothing for the 1st criteia
SEAU OS A1...0
In Home...192
TAT...280
Total...210
SEAU OS B1...0
Perhaps you could include what your expected outcome would be and how you arrived at that?
ok, using book 1
on Sheet1, cell A1 will have a drop down list of clients, e.g., INFINET SERVICE SOLUTIONS PTY LTD
so from sheet3,
1) column A has the client listed (cells A387-A434 is INFINET SERVICE SOLUTIONS PTY LTD)
2) I then want to look at only the 'In Home' jobs (cells B387-B398)
3) then only 'TAT' (Turn Around Time) (cells C395-C398)
4) then the 'Total' (cell D395)
5) then the week number (in Sheet 1 it would be B3-BB3 and the data is in Sheet3 A1-xx1)
so if we were talking about week 30 (cell Sheet1 AE3), then result would be 13.5 (Sheet3 AH398)
hope that makes sense
cheers
and apologies I forgot to add the week numbers in Sheet 1 (cells B3-BB3)
when I referred to SEAU OS A1, I meant the tab called 'SEAU OS' cell A1. same for the other SEAU OS.
cheers
I should add that I am trying to get the formula so that it doesn't need adjusting each wee as more data come through. so next week the data will go to week 31, then the week after week 32 will be added, etc.
I tried
=SUMIFS(Sheet3!1:1048576,Sheet3!A:A,A1,Sheet3!B:B,"In Home",Sheet3!C:C,"TAT",Sheet3!D:D,"Total",Sheet3!1:1,B2)
but get an #VALUE error
ok so did you upload an updated workbook?
updated file attached
no one able to assist?
cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks