Please help!

B

Bradders91

New Member
Joined
Mar 9, 2016
Messages
9
Hi

I was wondering if you are able to assist. I have a speadsheet which is pulling in country data and dates similar to the below but around 1000 entries with different combinations:
[TABLE="width: 79"]
<TBODY>[TR]
[TD][TABLE="width: 500"]
<TBODY>[TR]
[TD]A1
[/TD]
[TD]A2
[/TD]
[TD]A3
[/TD]
[/TR]
[TR]
[TD]Italy
[/TD]
[TD]31/03/2015
[/TD]
[TD]Complete
[/TD]
[/TR]
[TR]
[TD]Mexico
[/TD]
[TD]31/03/2015
[/TD]
[TD]In progress
[/TD]
[/TR]
[TR]
[TD]Italy
[/TD]
[TD]10/09/2015
[/TD]
[TD]In progress
[/TD]
[/TR]
[TR]
[TD]UK
[/TD]
[TD]19/02/2016
[/TD]
[TD]In progress
[/TD]
[/TR]
[TR]
[TD]India
[/TD]
[TD]19/02/2016
[/TD]
[TD]Not actioned
[/TD]
[/TR]
[TR]
[TD]UK
[/TD]
[TD]15/03/2015
[/TD]
[TD]Not actioned
[/TD]
[/TR]
[TR]
[TD]Greece
[/TD]
[TD]30/03/2016
[/TD]
[TD]Not actioned
[/TD]
[/TR]
[TR]
[TD]Italy
[/TD]
[TD]30/05/2016
[/TD]
[TD]Complete
[/TD]
[/TR]
[TR]
[TD]Greece
[/TD]
[TD]30/05/2016
[/TD]
[TD]Complete
[/TD]
[/TR]
</TBODY>[/TABLE]
</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]I am looking to count the number of entries which are not located in various countries, have a date after a specific date and have a specific status. For example i want to find out how many transactions are not in India or Greece, which have a date later than 30/12/2015 and have a status of complete.

Please could you help writing the formula. I have tried multiple ways but seem to get stuck each time.

Many thanks
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
Welcome to the forum!

How about:
Code: 
=SUMPRODUCT(--(A1:A9<>"India"),--(A1:A9<>"Greece"),--(B1:B9>DATEVALUE("30/12/2015")),--(C1:C9="Complete"))

Notice how all the ranges are the same length - if they're not, it won't work properly.

Pete
 
Last edited:
Upvote 0
Brilliant many thanks!

Is there any way that the forumla could also count if the date is blank?

Apologies I forgot to mention that part
 
Upvote 0
This is one of my attempts but it does'nt seem to work;

=SUM(COUNTIFS('Q1&Q2 Progress'!K:K,{"*India*","<>*Greece*"},'Q1&Q2 Progress'!R:R,{">31/12/2015",""},'Q1&Q2 Progress'!W:W,"Complete"))
 
Upvote 0
Bradders91 said:
This is one of my attempts but it does'nt seem to work;

=SUM(COUNTIFS('Q1&Q2 Progress'!K:K,{"*India*","<>*Greece*"},'Q1&Q2 Progress'!R:R,{">31/12/2015",""},'Q1&Q2 Progress'!W:W,"Complete"))
Click to expand...

Sorry the forumla I am trying is actuallyas follows not as above: =SUM(COUNTIFS('Q1&Q2 Progress'!K:K,{"<>*India*","<>*Greece*"},'Q1&Q2 Progress'!R:R,{">31/12/2015",""},'Q1&Q2 Progress'!W:W,"Complete"))
 
Upvote 0
[TABLE="width: 1204"]
<colgroup><col><col><col><col span="5"><col><col span="6"></colgroup><tbody>[TR]
[TD]Italy
[/TD]
[TD="align: right"]31/03/2015[/TD]
[TD]Complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mexico[/TD]
[TD="align: right"]31/03/2015[/TD]
[TD]In progress[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Italy[/TD]
[TD="align: right"]10/09/2015[/TD]
[TD]In progress[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]not in[/TD]
[TD][/TD]
[TD]after date[/TD]
[TD][/TD]
[TD]status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD]In progress[/TD]
[TD][/TD]
[TD]row 4[/TD]
[TD][/TD]
[TD]Greece[/TD]
[TD][/TD]
[TD="align: right"]30/12/2015[/TD]
[TD][/TD]
[TD]Complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]India[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD]Not actioned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]India[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD="align: right"]15/03/2015[/TD]
[TD]Not actioned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greece[/TD]
[TD="align: right"]30/03/2016[/TD]
[TD]Not actioned[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Italy[/TD]
[TD="align: right"]30/05/2016[/TD]
[TD]Complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greece[/TD]
[TD="align: right"]30/05/2016[/TD]
[TD]Complete[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]=SUMPRODUCT(($A$1:$A$9<>G4)*($A$1:$A$9<>G5)*($B$1:$B$9>I4)*($C$1:$C$9=K4))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]this is simple and pretty much self explanatory[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]you can change the search requirements in G4 G5 I4 K4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]and the answer will be correct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Here's a variation on mine where you might want to introduce an OR condition:
Code: 
=SUMPRODUCT(--(A1:A9<>"India"),--(A1:A9<>"Greece"),--(B1:B9>DATEVALUE("30/12/2015")),--(C1:C9="Complete")+(C1:C9="Not Actioned"))
Here, the status is EITHER "Complete" OR "Not Actioned".

I use SUMPRODUCT as against COUNTIFS/SUMIFS purely because it took me ages to figure out all its various permutations and now I couldn't do without it, although I believe that COUNTIFS/SUMIFS are faster than SUMPRODUCT.

Pete
 
Last edited:
Upvote 0
This worked for me...

Excel 2010
ABCDE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]A1[/TD]
[TD="bgcolor: #FFFFFF"]A2[/TD]
[TD="bgcolor: #FFFFFF"]A3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Your Answer:[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Italy[/TD]
[TD="bgcolor: #FFFFFF"]3/31/2015[/TD]
[TD="bgcolor: #FFFFFF"]Complete[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Mexico[/TD]
[TD="bgcolor: #FFFFFF"]3/31/2015[/TD]
[TD="bgcolor: #FFFFFF"]In progress[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Italy[/TD]
[TD="bgcolor: #FFFFFF"]9/10/2015[/TD]
[TD="bgcolor: #FFFFFF"]In progress[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]2/19/2016[/TD]
[TD="bgcolor: #FFFFFF"]In progress[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]India[/TD]
[TD="bgcolor: #FFFFFF"]2/19/2016[/TD]
[TD="bgcolor: #FFFFFF"]Not actioned[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]UK[/TD]
[TD="bgcolor: #FFFFFF"]3/15/2016[/TD]
[TD="bgcolor: #FFFFFF"]Not actioned[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]Greece[/TD]
[TD="bgcolor: #FFFFFF"]3/30/2016[/TD]
[TD="bgcolor: #FFFFFF"]Not actioned[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]Italy[/TD]
[TD="bgcolor: #FFFFFF"]5/30/2016[/TD]
[TD="bgcolor: #FFFFFF"]Complete[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]Greece[/TD]
[TD="bgcolor: #FFFFFF"]5/30/2016[/TD]
[TD="bgcolor: #FFFFFF"]Complete[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=COUNTIFS(A2:A10,"<>" & "India",A2:A10,"<>" & "Greece",B2:B10,">" & DATEVALUE("12/31/2015"),C2:C10,"Complete")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
214
flashgordie
F
L
  • Solved
Help Please
2 3
Replies
21
Views
701
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
325
Raj1979
R
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
107
Bernie Galve
B
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
535
dshtesy
D

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