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

 

[VBA Geek]

Hello - can you post a sample of your data

 

[azumi]

Maybe better if you describe the problem with the table for accurate results

 

[SaraPeeters]

I don't know how to paste my data here

 

[SaraPeeters]

[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]

 

[liveinhope]

Do you have excel 2007 or later ? If you do COUNTIFS makes this easy . I had to change your data a bit becuase nobody has the targets of 30% you were after

formula =COUNTIFS(D3:D7,"> 10",G3:G7,"> 10",J3:J7,"> 10") counts all rows (from 3 to 7) where D > 10 and G > 10 and J > 10 ( the answer = 1)

doesnt work for 2003 or earlier so thats where you are and you can update !!!
[TABLE="width: 518"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" span=7 width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 83, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]D[/TD]
[TD="width: 64, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 64, bgcolor: transparent"]G[/TD]
[TD="width: 64, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[TD="width: 96, bgcolor: transparent"]J[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl66, width: 83, bgcolor: white"]sesame 2013[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]sesame 2011[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]sesame %[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]maize 2013[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]maize 2011[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]maize %[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]sorgh 2013[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]sorgh 2011[/TD]
[TD="class: xl66, width: 96, bgcolor: white"]sorgh %[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 83, bgcolor: white"]300[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]280[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]7[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1300[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1350[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]-3.7[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl67, width: 96, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 83, bgcolor: white"]360[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]300[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]20[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1100[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]900[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]22.2[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1300[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]700[/TD]
[TD="class: xl67, width: 96, bgcolor: transparent"]85.7[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 83, bgcolor: white"]350[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]300[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]17[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1000[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]800[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]25[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl67, width: 96, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 83, bgcolor: white"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]700[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]710[/TD]
[TD="class: xl67, width: 96, bgcolor: transparent"]-1.4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"] [/TD]
[TD="class: xl65, width: 83, bgcolor: white"]340[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]300[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]10[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1250[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]1100[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"]13.6[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]730[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]700[/TD]
[TD="class: xl67, width: 96, bgcolor: transparent"]4.3[/TD]
[/TR]
</TBODY>[/TABLE]

 

[SaraPeeters]

This is want I also got, but that is not what i need. Now it looks for a farmer that has this increase for all crops. The problem is that some farmers have this increase in 1 crop, some in 2 crops and some in 3 crops. I want to count them if they have this increase in one of the crops. Secondly, if a farmer has an increase in 2 crops I want this farmer to be counted as one.

 

[VBA Geek]

Hello, you can download your worksheet here: Zippyshare.com - SaraPeters.xlsx

let me know if it helps

 

[liveinhope]

Hi Sara - in your original post you asked for

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.


ie farmers who met all 3 criteria - If you have excel 2007 or later the formula =COUNTIFS(D3:D7,"> 10",G3:G7,"> 10",J3:J7,"> 10") as per my last post does just that (change the cell references and targets - I used 10% as needed)

Do you mean that you would like a formulas to count farmers who have met their target in any one or the 3 crops and a formula to count those who met any 2 out of the 3 crops ?

 

[SaraPeeters]

Hi Sara - in your original post you asked for

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.


ie farmers who met all 3 criteria - If you have excel 2007 or later the formula =COUNTIFS(D3:D7,"> 10",G3:G7,"> 10",J3:J7,"> 10") as per my last post does just that (change the cell references and targets - I used 10% as needed)

Do you mean that you would like a formulas to count farmers who have met their target in any one or the 3 crops and a formula to count those who met any 2 out of the 3 crops ?

--> I need a formula that calculates the farmers with an increase in a crop (maize, sesame, sorghum) but in the same time if they have an increase in more than 1 crop this farmer has to be counted as one. Because with this formula it is counting only the farmers with increase in all crops.