Formula for transferring data from one sheet to another

[weaverswonders6]

Good afternoon! I am hoping someone can assist with an issue I am having with my monthly reporting. I need to have data transferred to a "bucket" of sorts on a 2nd sheet for an overview on my monthly report. On sheet 2, I have projects that I pull with the amount of time it took to close them and what manager's team they belonged to. On the 1st sheet, I have the managers all listed with the # of projects closed between 0&5 days, 5-10, and 10+. Right now, I am forced to filter and manually add these totals up every month. Can someone assist with a formula I could enter in which excel would transfer and then sum the totals for me from sheet to sheet? Attached is an example of what I am working with. Thank you so much in advance!

[SDCh]

See the attachment, may be this what you looking for

[weaverswonders6]

Ok, I am a bit of a novice here so can you help by explaining how/what you did to come up with those results? Also, your results are different from those I already came up with - were my numbers that far off?

[weaverswonders6]

Oh, I see you used the datedif function, however, the Days open tab in my example is the business time it took to complete the project. Tab H is the data I need to use to complete the buckets of 0-5 days, 5-10, etc...

I think I have it figured...I changed your formula to the H tab instead of I and F4'd it. THANKS!!!!

[SDCh]

Syntax : SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The SUMIFS function syntax has the following arguments:
sum_range (required): One or more cells to sum, including numbers or names, ranges, or cell references that contain numbers. Blank and text values are ignored.
criteria_range1 (required): The first range in which to evaluate the associated criteria.
criteria1 (required): The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added.
For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."
criteria_range2, criteria2, … (Optional): Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

for more information read this: http://office.microsoft.com/en-001/e...010047504.aspx


Regards,
SDCh

[weaverswonders6]

This is exquisite! I am being tasked with more and more reporting duties and this is the type of information that is invaluable to helping me save some time.

[weaverswonders6]

Follow-up question for you. I have attached another demo. Can I use the same SUMIFS for other data and not just numbers? In this instance, I am trying to pull the volume for new orders and existing orders for each manager as well based off the data in Column C on sheet 2. Any of those orders that begin with New Account should count for Volume New and all others as Existing Volume. I tried the following and got nada - =SUMIFS(TAT!$G:$G,TAT!$B:$B,Overview!A26,TAT!$C:$C,"="&New Account- NC MBS,TAT!$C:$C,"="&New Account- EC MBS, TAT!$C:$C,"="&New Account- NC LN,TAT!$C:$C,"="&New Account- EC Bridger,TAT!$C:$C,"="&New Account- NC Bridger,TAT!$C:$C,"="&New Account- NC RW,TAT!$C:$C,"="&New Account- NC DM,TAT!$C:$C,"="&New Account- EC CP,TAT!$C:$C,"="&New Account- EC LN,TAT!$C:$C,"="&New Account- EC RW)

[oeldere]

with an helpcolum in your data.

with VLookup you get the criteria.

after that just use a pivot table.

see the attached file.

[SDCh]

Oh, sorry don't know about that, just change to this:

=SUMIFS(TAT!$G$2:$G$1037,TAT!$B$2:$B$1037,Overview!A4,TAT!$H$2:$H$1037,"<="& 5)

=SUMIFS(TAT!$G$2:$G$1037,TAT!$B$2:$B$1037,Overview!A4,TAT!$H$2:$H$1037,">"& 5,TAT!$H$2:$H$1037,"<=" &10)

=SUMIFS(TAT!$G$2:$G$1037,TAT!$B$2:$B$1037,Overview!A4,TAT!$H$2:$H$1037,">"& 10)

[SDCh]

Nice to know your problem will be solved and thanks for the rep.

By the way, please mark this thread as solved.

[SDCh]

You want sum all Project Type Code that begin with "New Account-" on column "Volume New"?
try this:

=SUMIFS(TAT!$G2:$G1037,TAT!$B2:$B1037,Overview!A26,TAT!$C2:$C1037,"New Account*")

and on "Volume Existing"

=SUMIFS(TAT!$G2:$G1037,TAT!$B2:$B1037,Overview!A26,TAT!$C2:$C1037,"<>New Account*")

[weaverswonders6]

Boom - that is amazing and I can not thank you nearly enough! Thanks again for all of your help