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
date
Name
Result
3/1/2012
Anna
Pending
3/1/2012
John
Closed
3/1/2012
Sam
Reassinged
3/1/2012
John
Reassinged
4/1/2012
Anna
Pending
4/1/2012
Anna
Pending
4/1/2012
Anna
Closed
4/1/2012
Anna
Closed
4/1/2012
Sam
Reassinged

<TBODY>
</TBODY>



The result should give me this in Tab 2

3/1/2012
3/1/2012
3/1/2012
4/1/2012
4/1/2012
4/1/2012
Pending
Closed
Reassinged
Pending
Closed
Reassinged
Anna
1
2
1
John
1
1
1
Sam
1
1

<TBODY>
</TBODY>

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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
date
Name
Result
3/1/2012
Anna
Pending
3/1/2012
John
Closed
3/1/2012
Sam
Reassinged
3/1/2012
John
Reassinged
4/1/2012
Anna
Pending
4/1/2012
Anna
Pending
4/1/2012
Anna
Closed
4/1/2012
Anna
Closed
4/1/2012
Sam
Reassinged

<tbody>
</tbody>



The result should give me this in Tab 2

3/1/2012
3/1/2012
3/1/2012
4/1/2012
4/1/2012
4/1/2012
Pending
Closed
Reassinged
Pending
Closed
Reassinged
Anna
1
2
1
John
1
1
1
Sam
1
1

<tbody>
</tbody>

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:
3/1/20123/1/20123/1/20124/1/20124/1/20124/1/2012
PendingClosedReassingedPendingClosedReassinged
Anna100220
John011000
Sam001001

<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>
</tbody>

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
ABCDEFG
13/1/20123/1/20123/1/20124/1/20124/1/20124/1/2012
2PendingClosedReassignedPendingClosedReassigned
3Anna100220
4John011000
5Sam00101


<tbody>
</tbody>

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...
Unassinged TT02/09/201202/09/201202/09/201203/09/201203/09/201204/09/201204/09/2012
Agent NameTT's HandledPendingReassingedClosedPendingReassingedPendingReassinged
Ajay31100000
Allen00000000
Asam00000000
Asim00000000

<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>
</tbody>

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

V
  • Solved
Filter or Formula for Last Work
Replies
3
Views
282
Peter_SSs
Peter_SSs
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
230
awoohaw
awoohaw
Ramballah
  • Question
Need a formula that counts the shifts and gives me a total
Replies
2
Views
326
bobaol
B
U
  • Solved
Formula that pulls date from other tab, but tab selected can vary?
Replies
2
Views
53
usnapoleon
U
R
  • Question
Count two values in different cells within the same row
2
Replies
10
Views
583
Fluff
Fluff

Forum statistics

Threads
1,216,050
Messages
6,128,498
Members
449,455
Latest member
jesski

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