Please help!

[Bradders91]

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:

A1 A2 A3 Italy 31/03/2015 Complete Mexico 31/03/2015 In progress Italy 10/09/2015 In progress UK 19/02/2016 In progress India 19/02/2016 Not actioned UK 15/03/2015 Not actioned Greece 30/03/2016 Not actioned Italy 30/05/2016 Complete Greece 30/05/2016 Complete <TBODY> </TBODY> </SPAN>

</SPAN>

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

</SPAN>

</SPAN>

</SPAN>

</SPAN>

</SPAN>

</SPAN>

<TBODY>
</TBODY>

 

[NewOrderFac33]

Welcome to the forum!

How about:

=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

 

[jim may]

Take a look at the Countifs() function...

 

[Bradders91]

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

 

[Bradders91]

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"))

 

[Bradders91]

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"))

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"))

 

[oldbrewer]

Italy

31/03/2015

Complete

col G

Mexico

31/03/2015

In progress

Italy

10/09/2015

In progress

not in

after date

status

UK

19/02/2016

In progress

row 4

Greece

30/12/2015

Complete

India

19/02/2016

Not actioned

India

UK

15/03/2015

Not actioned

Greece

30/03/2016

Not actioned

Italy

30/05/2016

Complete

Greece

30/05/2016

Complete

1

formula giving 1

=SUMPRODUCT(($A$1:$A$9<>G4)*($A$1:$A$9<>G5)*($B$1:$B$9>I4)*($C$1:$C$9=K4))

this is simple and pretty much self explanatory

you can change the search requirements in G4 G5 I4 K4

and the answer will be correct

<colgroup><col><col><col><col span="5"><col><col span="6"></colgroup><tbody>
</tbody>

 

[NewOrderFac33]

Here's a variation on mine where you might want to introduce an OR condition:

=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

 

[jim may]

This worked for me...

Excel 2010

	A	B	C	D	E
1	A1	A2	A3		Your Answer:
2	Italy	3/31/2015	Complete		1
3	Mexico	3/31/2015	In progress
4	Italy	9/10/2015	In progress
5	UK	2/19/2016	In progress
6	India	2/19/2016	Not actioned
7	UK	3/15/2016	Not actioned
8	Greece	3/30/2016	Not actioned
9	Italy	5/30/2016	Complete
10	Greece	5/30/2016	Complete

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =COUNTIFS(A2:A10,"<>" & "India",A2:A10,"<>" & "Greece",B2:B10,">" & DATEVALUE("12/31/2015"),C2:C10,"Complete") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>