help with creating a formula

A

ash1212002

New Member
Joined
Jul 21, 2015
Messages
10
Hi all

I hoping you can kindly assist me on this great forum!

I am trying to create a formula which gives me a total figure of an amount of tickets that are 'pending' in a specific date range, i have tried various formulas with no success, the date field contains a timestamp which needs to be excluded in the results

to summarise i need to know how many tickets are in 'pending' status on '21/07/2015'



Could you assist me with the formula please
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Welcome to the board. No idea what columns your data is in so this is a guess, however, try:
Code: 
=SUMIFS(H1:H10,A1:A10,"Pending",D1:D10,"21/07/2105")
 
Upvote 0
JackDanIce said:
Welcome to the board. No idea what columns your data is in so this is a guess, however, try:
Code: 
=SUMIFS(H1:H10,A1:A10,"Pending",D1:D10,"21/07/2105")
Click to expand...

Morning Jack, I've tried the formula but I am getting an error message of #VALUE!

Column D I have the status data and column H I have the date data

StatusCreated Time
Closed2015-07-09 07:50:04
Closed2015-07-09 07:50:04
Closed2015-07-09 07:59:09
Closed2015-07-09 08:30:30
Closed2015-07-09 09:01:59
Awaiting Customer Response2015-07-09 09:59:06
Resolved2015-07-09 10:27:52
Awaiting Customer Response2015-07-09 10:37:18
Waiting on Third Party2015-07-09 10:41:56

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Then it's a COUNTIFS function, not SUMIFS. Try:
Code: 
=COUNTIFS($D$1:$D$25,"Pending",$H$1:$H$25,"21/07/2015")
 
Upvote 0
Try
I4 = Date 21/7/15
=SUMPRODUCT((D2:D11="Pending")*(TEXT(H2:H11,"ddmmyy")=TEXT(I14,"ddmmyy")))
 
Upvote 0
Without seeing your data, I don't know what the answer is I'm afraid. Have you tried gaz_chops' suggestion?
 
Upvote 0
JackDanIce said:
Without seeing your data, I don't know what the answer is I'm afraid. Have you tried gaz_chops' suggestion?
Click to expand...

does this help?

StatusCreated Time Closed TimeLast Updated Time
Closed20/07/2015 2015-07-15 00:51:242015-07-15 10:08:09
Closed20/07/2015 2015-07-14 05:56:572015-07-15 10:08:09
Closed2015-07-09 07:59:09 2015-07-10 15:51:002015-07-15 10:08:09
Closed2015-07-09 08:30:30 2015-07-14 09:18:552015-07-14 09:18:55
Closed2015-07-09 09:01:59 2015-07-20 08:52:172015-07-20 08:52:17
Awaiting Customer Response2015-07-09 09:59:062015-07-15 08:51:17
Resolved2015-07-09 10:27:522015-07-09 12:15:08
Awaiting Customer Response2015-07-09 10:37:182015-07-15 10:08:09
Waiting on Third Party2015-07-09 10:41:562015-07-16 14:59:16
Closed2015-07-09 10:45:042015-07-14 18:55:102015-07-14 18:55:10

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Formula to exclude data when using Today
Replies
2
Views
249
ExceLoki
ExceLoki
M
  • Question
Calculating Percentage between two columns
Replies
0
Views
1K
Mr Steve Cox
M
D
  • Question
Assistance with Formula
Replies
2
Views
113
DWay13
D
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
345
KRice
K
S
  • Solved
Formula to Calculate a due date with excluding dates
Replies
5
Views
318
Fluff
Fluff

Forum statistics

Threads
1,214,989
Messages
6,122,622
Members
449,093
Latest member
catterz66

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