Total of amount of tickets for agent and date

[Jamesraywebber85]

I have a sheet called "ART Weekly Numbers" that shows how many tickets an agent does per day & the number of kickbacks. I need a formula to sum those numbers on sheet "ART Yearly Numbers" off the date and return to the cell that is for that agent and then put the kickbacks from that day into the below cell. I have attached a workbook so you can see what is needed. Thank you in advance for any help!

[JohnTopley]

A "starter"

in C5 of "ART Yearly Numbers"

=SUMIF($B$16:$B$100,$B5,C$16:C$100)

Copy across and down

[JohnTopley]

"Art Weekly Numbers" gets data from "Arts Yearly Numbers" ???????

So I am confused as it appears to be going round in circles.

[Jamesraywebber85]

Correct - so "ART Yearly Numbers pull from the agents sheets (VBA Macro) and then the weekly should pull the numbers of that sheet to create a weekly view!

[Jamesraywebber85]

I don't think the formula you provided is correct - it keeps giving me a reference error when applying that formula

[JohnTopley]

Se C5 in attached:

And please add sample data to your file as I still confused as to what is going where.

[Jamesraywebber85]

Ok here is an example file with data in "ART Yearly Numbers". What I want the weekly numbers to show for example:

Hector Almodovar-Cruz on sheet("ART Weekly Numbers").Range("F20") = 32 (sum of EC16:EC20)
Hector Almodovar-Cruz on sheet("ART Weekly Numbers").Range("F21") = 4
(Sum of EC21)

and so on for each agent. I have included the workbook for your reference with sample data.

[Jamesraywebber85]

Would maybe a index/match with a sum work?

[JohnTopley]

In B20 of "Weekly"

=SUM(INDEX('ART Yearly Numbers'!$C$16:$NC$100,MATCH('ART Weekly Numbers'!$A$18,'ART Yearly Numbers'!$A$16:$A$100,0),MATCH('ART Weekly Numbers'!B$19,'ART Yearly Numbers'!$C$15:$NC$15,0)):INDEX('ART Yearly Numbers'!$C$16:$NC$100,MATCH('ART Weekly Numbers'!$A$18,'ART Yearly Numbers'!$A$16:$A$100,0)+4,MATCH('ART Weekly Numbers'!B$19,'ART Yearly Numbers'!$C$15:$NC$15,0)))

OR

=SUM(OFFSET('ART Yearly Numbers'!$A$15,MATCH('ART Weekly Numbers'!$A$18,'ART Yearly Numbers'!$A$16:$A$100,0),MATCH('ART Weekly Numbers'!B$19,'ART Yearly Numbers'!$C$15:$NC$15,0)+1,5))

OFFSET is volatile.

Copy across

in B21

=INDEX('ART Yearly Numbers'!$A$1:$NC$99,MATCH($A$18,'ART Yearly Numbers'!$A$1:$A$99,0)+5,MATCH(B$19,'ART Yearly Numbers'!$A$4:$NC$4,0))

Your layout makes it very difficult to reproduce these formulae simply by dragging across and/or down. Plus you use merged cells.

Highlighted data are those that need adjusting for each person.



in the previous file I sent I started a more "Excel" friendly form in "ART Weekly Numbers (2)"

[Jamesraywebber85]

Awesome - works like a charm - thank you very much!