Need data count using formula in a different tab to give me the result

S

starpupil

New Member
Joined
Jan 30, 2012
Messages
10
i am looking to get a formula which will help me get a count of number of times the event has occured
example

This data is found in Tab 1
[TABLE="class: grid, width: 12, align: center"]
<TBODY>[TR]
[TD]date
[/TD]
[TD]Name
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]John
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]Sam
[/TD]
[TD]Reassinged
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]John
[/TD]
[TD]Reassinged
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Sam
[/TD]
[TD]Reassinged
[/TD]
[/TR]
</TBODY>[/TABLE]



The result should give me this in Tab 2

[TABLE="class: grid, width: 50, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]3/1/2012
[/TD]
[TD]3/1/2012
[/TD]
[TD]3/1/2012
[/TD]
[TD]4/1/2012
[/TD]
[TD]4/1/2012
[/TD]
[TD]4/1/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pending
[/TD]
[TD]Closed
[/TD]
[TD]Reassinged
[/TD]
[TD]Pending
[/TD]
[TD]Closed
[/TD]
[TD]Reassinged
[/TD]
[/TR]
[TR]
[TD]Anna
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
</TBODY>[/TABLE]

I tried to different variation of =countif; then i do not know how to put the date variable into account
please help,

much appreciated
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
starpupil said:
i am looking to get a formula which will help me get a count of number of times the event has occured
example

This data is found in Tab 1
[TABLE="class: grid, width: 12, align: center"]
<tbody>[TR]
[TD]date
[/TD]
[TD]Name
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]John
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]Sam
[/TD]
[TD]Reassinged
[/TD]
[/TR]
[TR]
[TD]3/1/2012
[/TD]
[TD]John
[/TD]
[TD]Reassinged
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Pending
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Anna
[/TD]
[TD]Closed
[/TD]
[/TR]
[TR]
[TD]4/1/2012
[/TD]
[TD]Sam
[/TD]
[TD]Reassinged
[/TD]
[/TR]
</tbody>[/TABLE]



The result should give me this in Tab 2

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]3/1/2012
[/TD]
[TD]3/1/2012
[/TD]
[TD]3/1/2012
[/TD]
[TD]4/1/2012
[/TD]
[TD]4/1/2012
[/TD]
[TD]4/1/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pending
[/TD]
[TD]Closed
[/TD]
[TD]Reassinged
[/TD]
[TD]Pending
[/TD]
[TD]Closed
[/TD]
[TD]Reassinged
[/TD]
[/TR]
[TR]
[TD]Anna
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I tried to different variation of =countif; then i do not know how to put the date variable into account
please help,

much appreciated
Click to expand...

A1:C10, Sheet1, houses the data, the headers in the first row included.

A1:G5, Sheet2, houses the processing:
[TABLE="width: 507"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 94pt; mso-width-source: userset; mso-width-alt: 4437;" width="125"> <col style="width: 73pt; mso-width-source: userset; mso-width-alt: 3470;" width="98"> <col style="width: 72pt; mso-width-source: userset; mso-width-alt: 3413;" width="96"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;" width="90"> <col style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;" width="108"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3384;" width="95"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 125, bgcolor: white"]3/1/2012[/TD]
[TD="class: xl64, width: 98, bgcolor: white"]3/1/2012[/TD]
[TD="class: xl64, width: 96, bgcolor: white"]3/1/2012[/TD]
[TD="class: xl64, width: 90, bgcolor: white"]4/1/2012[/TD]
[TD="class: xl64, width: 108, bgcolor: white"]4/1/2012[/TD]
[TD="class: xl64, width: 95, bgcolor: white"]4/1/2012[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 125, bgcolor: white"]Pending[/TD]
[TD="class: xl63, width: 98, bgcolor: white"]Closed[/TD]
[TD="class: xl63, width: 96, bgcolor: white"]Reassinged[/TD]
[TD="class: xl63, width: 90, bgcolor: white"]Pending[/TD]
[TD="class: xl63, width: 108, bgcolor: white"]Closed[/TD]
[TD="class: xl63, width: 95, bgcolor: white"]Reassinged[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Anna[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]John[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Sam[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]

If on Excel 2007 or later...

B3, just enter, copy across, and down:
Rich (BB code): 
=COUNTIFS(
  Sheet1!$A$2:$A$10,">="&B$1,
  Sheet1!$A$2:$A$10,"<="&EOMONTH(B$1,0),
  Sheet1!$B$2:$B$10,$A3,
  Sheet1!$C$2:$C$10,B$2)

On all system3...

B3, just enter, copy across, and down:
Rich (BB code): 
=SUMPRODUCT(
  --(Sheet1!$A$2:$A$10-DAY(Sheet1!$A$2:$A$10)+1=B$1),
  --(Sheet1!$B$2:$B$10=$A3),
  --(Sheet1!$C$2:$C$10=B$2))

Custom format the formula cells as e.g.: [=0]"";General
 
Upvote 0
[TABLE="class: grid, width: 500"]

<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]3/1/2012[/TD]
[TD]3/1/2012[/TD]
[TD]3/1/2012[/TD]
[TD]4/1/2012[/TD]
[TD]4/1/2012[/TD]
[TD]4/1/2012[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Pending[/TD]
[TD]Closed[/TD]
[TD]Reassigned[/TD]
[TD]Pending[/TD]
[TD]Closed[/TD]
[TD]Reassigned[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Anna[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sam[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>
[/TABLE]

In B3 use:
=COUNTIFS('Tab 1'!$A$2:$A$10,B$1,'Tab 1'!$C$2:$C$10,B$2,'Tab 1'!$B$2:$B$10,$A3)
Change "Tab 1" to the name of your source sheet.
Drag to copy down then across.

Vidar
 
Upvote 0
Hi, thanks for the help, but now the coloumn that i am concerned are in
Date is in B,
Agent in in H and Status is in J

How do i modify to it, i tried this =COUNTIFS('Unassinged TT'!$H$36:$H$7777,K$36,'Unassinged TT'!$H$36:$H$7777,C$2,'Unassinged TT'!$H$36:$H$7777,"Ajay") as Ajay is the name of the agent i am looking for. didnt work. also 36 is start of the excel sheet count for me since the previous ones are used to creating drop downs.
Please advice
 
Upvote 0
Hi, could there be trailing or leading spaces in in the cells that contains "Ajay"?
What result tdo you get with the formula?

`Vidar
 
Upvote 0
starpupil said:
Hi, thanks for the help, but now the coloumn that i am concerned are in
Date is in B,
Agent in in H and Status is in J

How do i modify to it, i tried this =COUNTIFS('Unassinged TT'!$H$36:$H$7777,K$36,'Unassinged TT'!$H$36:$H$7777,C$2,'Unassinged TT'!$H$36:$H$7777,"Ajay") as Ajay is the name of the agent i am looking for. didnt work. also 36 is start of the excel sheet count for me since the previous ones are used to creating drop downs.
Please advice
Click to expand...

If the dates are in B, the agents in H, and the status values in J, the formula would become...
Rich (BB code): 
=COUNTIFS(
  'Unassigned TT'!$B$36:$B$7777,">="&B$1,
  'Unassigned TT'!$B$36:$B$7777,"<="&EOMONTH(B$1,0),
  'Unassigned TT'!$H$36:$H$7777,$A3,
  'Unassigned TT'!$J$36:$J$7777,B$2)

The formula assumes the same ouput lay-out as before: A3 houses an agent of interest like Ajay, B1 a first day date of a month year (dates in the data source can be any day of a month year), and B2 a status value.

Hope this set up matches your ranges and output lay-out.
 
Upvote 0
starpupil said:
Hi, thanks for the help, but now the coloumn that i am concerned are in
Date is in B,
Agent in in H and Status is in J

How do i modify to it, i tried this =COUNTIFS('Unassinged TT'!$H$36:$H$7777,K$36,'Unassinged TT'!$H$36:$H$7777,C$2,'Unassinged TT'!$H$36:$H$7777,"Ajay") as Ajay is the name of the agent i am looking for. didnt work. also 36 is start of the excel sheet count for me since the previous ones are used to creating drop downs.
Please advice
Click to expand...

Aladin Akyurek said:
If the dates are in B, the agents in H, and the status values in J, the formula would become...
Rich (BB code): 
=COUNTIFS(
  'Unassigned TT'!$B$36:$B$7777,">="&B$1,
  'Unassigned TT'!$B$36:$B$7777,"<="&EOMONTH(B$1,0),
  'Unassigned TT'!$H$36:$H$7777,$A3,
  'Unassigned TT'!$J$36:$J$7777,B$2)

The formula assumes the same ouput lay-out as before: A3 houses an agent of interest like Ajay, B1 a first day date of a month year (dates in the data source can be any day of a month year), and B2 a status value.

Hope this set up matches your ranges and output lay-out.
Click to expand...

Unmerge the date cells in your Summary & Reports and repeat the dates in the relevant cells...
[TABLE="width: 640"]
<colgroup><col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4494;" span="2" width="126"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;" width="94"> <col style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;" width="109"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 2702;" width="76"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 2986;" width="84"> <col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" width="79"> <tbody>[TR]
[TD="class: xl65, width: 252, bgcolor: #E26B0A, colspan: 2"]Unassinged TT[/TD]
[TD="class: xl67, width: 94, bgcolor: transparent, align: right"]02/09/2012[/TD]
[TD="class: xl67, width: 109, bgcolor: transparent, align: right"]02/09/2012[/TD]
[TD="class: xl67, width: 79, bgcolor: transparent, align: right"]02/09/2012[/TD]
[TD="class: xl67, width: 76, bgcolor: transparent, align: right"]03/09/2012[/TD]
[TD="class: xl67, width: 79, bgcolor: transparent, align: right"]03/09/2012[/TD]
[TD="class: xl67, width: 84, bgcolor: transparent, align: right"]04/09/2012[/TD]
[TD="class: xl68, width: 79, bgcolor: transparent, align: right"]04/09/2012[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #E26B0A"]Agent Name[/TD]
[TD="class: xl70, bgcolor: #E26B0A"]TT's Handled[/TD]
[TD="class: xl71, bgcolor: transparent"]Pending[/TD]
[TD="class: xl71, bgcolor: transparent"]Reassinged[/TD]
[TD="class: xl71, bgcolor: transparent"]Closed[/TD]
[TD="class: xl71, bgcolor: transparent"]Pending[/TD]
[TD="class: xl71, bgcolor: transparent"]Reassinged[/TD]
[TD="class: xl71, bgcolor: transparent"]Pending[/TD]
[TD="class: xl71, bgcolor: transparent"]Reassinged[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #92CDDC"]Ajay[/TD]
[TD="class: xl73, bgcolor: #948A54"]3[/TD]
[TD="class: xl74, width: 94, bgcolor: transparent"]1[/TD]
[TD="class: xl74, width: 109, bgcolor: transparent"]1[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 84, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #92CDDC"]Allen[/TD]
[TD="class: xl73, bgcolor: #948A54"]0[/TD]
[TD="class: xl74, width: 94, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 109, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 84, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #92CDDC"]Asam[/TD]
[TD="class: xl73, bgcolor: #948A54"]0[/TD]
[TD="class: xl74, width: 94, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 109, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 84, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: #92CDDC"]Asim[/TD]
[TD="class: xl73, bgcolor: #948A54"]0[/TD]
[TD="class: xl74, width: 94, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 109, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 76, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 84, bgcolor: transparent"]0[/TD]
[TD="class: xl74, width: 79, bgcolor: transparent"]0[/TD]
[/TR]
</tbody>[/TABLE]

Since you want a summary multiconditional count based on individula dates in F3, G3, and so on...

In F5 enter, copy across, and down:
Rich (BB code): 
=COUNTIFS(
    'Unassinged TT'!$B$36:$B$7777,"="&F$3,
    'Unassinged TT'!$H$36:$H$7777,$D5,
    'Unassinged TT'!$J$36:$J$7777,F$4)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Hello! Please help me make my workbook better
Replies
3
Views
199
Micron
M
V
  • Solved
Filter or Formula for Last Work
Replies
3
Views
394
Peter_SSs
Peter_SSs
R
  • Question Question
Why Does this Sumproduct Formula Give a Different Result to Countifs?
Replies
3
Views
155
Alex Blakenburg
A
C
  • Question Question
Searching using TextBox and ListBox
Replies
1
Views
197
campbell1093
C
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
479
awoohaw
awoohaw

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top