Help with a difficult Countif syntax

[SaraPeeters]

Dear Excel lovers

I need some help of a syntax.
I have a database with respondents (farmers) that produce different crops (maize, sesame and sorghum). The have produced in 2011 and 2013 and i made a column with the increase in % --> 2013-2011)/2011

Now I want to count the farmers that have an increase of >=30% in maize + increase of >=30% in sesame + increase of >=30% in sorghum. But some farmers have that increase in both maize and sorghum, or sesame and maize. I do not want to count those farmers twice, since it will be double counting.

I know how to filter and calculate it manually, but it would be better if i can use a formula that generates the number automatic.

Can anybody help me, because I cannot find my answer online.

Greetings, Sara

 

[liveinhope]

I have added 2 helper columns to your original table.

Original table has cols A-C (critical column C % change), maize cols D-F (critcal column F % change) and sorghum in cols H-I ( critical col %change in I)

My current examle shows how many farmers achieved a 10% target in at least one crop but you should be able to change this easily to fir your needs

I added
- column J =($C3 > 10) +($F3>10) + ($I3>10) . shows how many of cells c3 ,F3 and I3 had a value of > 10 (change the "10" to whatever is your desired criteria)

- column K =(($C3>10)+($F3>10)+($I3>10)>=1) true or false . Is the total number of cells in (c3,F3, and I3) that passed the "10" test greater than or equal to 1 ?.

If you want to know if the number of cells passing the test is exactly 1 then use =(($C3>10)+($F3>10)+($I3>10)=1)


formula is m1 =COUNTIF(K3:K10,TRUE) .counts number of cells in K range with "TRUE" value

You don't actually need column J as the formula is K does all the necessary calcs in one step but it does help show how the overall formula works... delete this extra column once you're happy it works


[TABLE="width: 1159"]
<TBODY>[TR]
[TD]a</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]G</SPAN>
[/TD]
[TD]H</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]J</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD]# FARMERS </SPAN>
[/TD]
[/TR]
[TR]
[TD]sesame 2013</SPAN>
[/TD]
[TD]sesame 2011</SPAN>
[/TD]
[TD]sesame %</SPAN>
[/TD]
[TD]maize 2013</SPAN>
[/TD]
[TD]maize 2011</SPAN>
[/TD]
[TD]maize %</SPAN>
[/TD]
[TD]sorgh 2013</SPAN>
[/TD]
[TD]sorgh 2011</SPAN>
[/TD]
[TD]sorgh %</SPAN>
[/TD]
[TD]# indiv targets met</SPAN>
[/TD]
[TD]OVERALL target met ?</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300</SPAN>
[/TD]
[TD]280</SPAN>
[/TD]
[TD]7</SPAN>
[/TD]
[TD]1300</SPAN>
[/TD]
[TD]1350</SPAN>
[/TD]
[TD]-3.7</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]FALSE</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]360</SPAN>
[/TD]
[TD]300</SPAN>
[/TD]
[TD]20</SPAN>
[/TD]
[TD]1100</SPAN>
[/TD]
[TD]900</SPAN>
[/TD]
[TD]22.2</SPAN>
[/TD]
[TD]1300</SPAN>
[/TD]
[TD]700</SPAN>
[/TD]
[TD]85.7</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]350</SPAN>
[/TD]
[TD]300</SPAN>
[/TD]
[TD]17</SPAN>
[/TD]
[TD]1000</SPAN>
[/TD]
[TD]800</SPAN>
[/TD]
[TD]25</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]700</SPAN>
[/TD]
[TD]710</SPAN>
[/TD]
[TD]-1.4</SPAN>
[/TD]
[TD]0</SPAN>
[/TD]
[TD]FALSE</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]340</SPAN>
[/TD]
[TD]300</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]1250</SPAN>
[/TD]
[TD]1100</SPAN>
[/TD]
[TD]13.6</SPAN>
[/TD]
[TD]730</SPAN>
[/TD]
[TD]700</SPAN>
[/TD]
[TD]4.3</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]11</SPAN>
[/TD]
[TD]200</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]70</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]-30</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]TRUE</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Aladin Akyurek]

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/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]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]sesame[/TD]
[TD="align: center"]sesame[/TD]
[TD="align: center"]sesame[/TD]
[TD="align: center"]maize[/TD]
[TD="align: center"]maize[/TD]
[TD="align: center"]maize[/TD]
[TD="align: center"]sorgh[/TD]
[TD="align: center"]sorgh[/TD]
[TD="align: center"]sorgh[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]2013[/TD]
[TD="align: center"]2011[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]2013[/TD]
[TD="align: center"]2011[/TD]
[TD="align: center"]%[/TD]
[TD="align: center"]2013[/TD]
[TD="align: center"]2011[/TD]
[TD="align: center"]%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]280[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]1350[/TD]
[TD="align: center"]-3.7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]320[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]900[/TD]
[TD="align: center"]22.2[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]85.7[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]350[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]710[/TD]
[TD="align: center"]-1.4[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]340[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1250[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]13.6[/TD]
[TD="align: center"]730[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]4.3[/TD]
[/TR]
</tbody>[/TABLE]

What is the expected count for this exhibit? Also, is there a different farmer associated for each row?

 

[SaraPeeters]

I have added 2 helper columns to your original table.

Original table has cols A-C (critical column C % change), maize cols D-F (critcal column F % change) and sorghum in cols H-I ( critical col %change in I)

My current examle shows how many farmers achieved a 10% target in at least one crop but you should be able to change this easily to fir your needs

I added
- column J =($C3 > 10) +($F3>10) + ($I3>10) . shows how many of cells c3 ,F3 and I3 had a value of > 10 (change the "10" to whatever is your desired criteria)

- column K =(($C3>10)+($F3>10)+($I3>10)>=1) true or false . Is the total number of cells in (c3,F3, and I3) that passed the "10" test greater than or equal to 1 ?.

If you want to know if the number of cells passing the test is exactly 1 then use =(($C3>10)+($F3>10)+($I3>10)=1)


formula is m1 =COUNTIF(K3:K10,TRUE) .counts number of cells in K range with "TRUE" value

You don't actually need column J as the formula is K does all the necessary calcs in one step but it does help show how the overall formula works... delete this extra column once you're happy it works


[TABLE="width: 1159"]
<tbody>[TR]
[TD]a[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]# FARMERS [/TD]
[/TR]
[TR]
[TD]sesame 2013[/TD]
[TD]sesame 2011[/TD]
[TD]sesame %[/TD]
[TD]maize 2013[/TD]
[TD]maize 2011[/TD]
[TD]maize %[/TD]
[TD]sorgh 2013[/TD]
[TD]sorgh 2011[/TD]
[TD]sorgh %[/TD]
[TD]# indiv targets met[/TD]
[TD]OVERALL target met ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]280[/TD]
[TD]7[/TD]
[TD]1300[/TD]
[TD]1350[/TD]
[TD]-3.7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]360[/TD]
[TD]300[/TD]
[TD]20[/TD]
[TD]1100[/TD]
[TD]900[/TD]
[TD]22.2[/TD]
[TD]1300[/TD]
[TD]700[/TD]
[TD]85.7[/TD]
[TD]3[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]350[/TD]
[TD]300[/TD]
[TD]17[/TD]
[TD]1000[/TD]
[TD]800[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]700[/TD]
[TD]710[/TD]
[TD]-1.4[/TD]
[TD]0[/TD]
[TD]FALSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]340[/TD]
[TD]300[/TD]
[TD]10[/TD]
[TD]1250[/TD]
[TD]1100[/TD]
[TD]13.6[/TD]
[TD]730[/TD]
[TD]700[/TD]
[TD]4.3[/TD]
[TD]1[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]100[/TD]
[TD]11[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]70[/TD]
[TD]100[/TD]
[TD]-30[/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for your quick response! I have used your column J. The other column (K) was not needed since I got my answer with the following formula:
=COUNTIF(J3:J10,">=1")

 

[liveinhope]

Good to hear it worked out for you

I was actually going to suggest an enhancement on my first version which may be of interest

- at the moment my formulas are hard-coded to look for farmers who met theur targets in one or more crops . if you want to look for farmers who got targets in 2 or more crops (or in all 3 crops) you have to change the formula

- suggested enhancement - in a cell m2 enter the number of targets you want to test on eg to see farmers who met 2 or targets enter 2 . then (and you will need to put the K formula back change my old formula =(($C3>10)+($F3>10)+($I3>10)>=1) to =(($C3>10)+($F3>10)+($I3>10)>=$m$2) (where $m$2 is the cell containing you "how many targets do the farmers need to meet ?" figure)

- in the same way you can also replace the hard-coded 10 target with a cell (eg m3) where you enter the target and then modify the formula to fit

 

[SaraPeeters]

Good to hear it worked out for you

I was actually going to suggest an enhancement on my first version which may be of interest

- at the moment my formulas are hard-coded to look for farmers who met theur targets in one or more crops . if you want to look for farmers who got targets in 2 or more crops (or in all 3 crops) you have to change the formula

- suggested enhancement - in a cell m2 enter the number of targets you want to test on eg to see farmers who met 2 or targets enter 2 . then (and you will need to put the K formula back change my old formula =(($C3>10)+($F3>10)+($I3>10)>=1) to =(($C3>10)+($F3>10)+($I3>10)>=$m$2) (where $m$2 is the cell containing you "how many targets do the farmers need to meet ?" figure)

- in the same way you can also replace the hard-coded 10 target with a cell (eg m3) where you enter the target and then modify the formula to fit

126.415.31.531 times thanks!!

 

[liveinhope]

youre welcome