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]

	A	B	C	D	E	F	G	H	I
1	sesame	sesame	sesame	maize	maize	maize	sorgh	sorgh	sorgh
2	2013	2011	%	2013	2011	%	2013	2011	%
3	300	280	7	1300	1350	-3.7	0	0	0
4	320	300	7	1100	900	22.2	1300	700	85.7
5	350	300	17	1000	800	25	0	0	0
6	0	0	0	0	0	0	700	710	-1.4
7	340	300	10	1250	1100	13.6	730	700	4.3

<tbody>
</tbody>

 

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

	B	C	D	E	F	G	H	I	J
	sesame 2013	sesame 2011	sesame %	maize 2013	maize 2011	maize %	sorgh 2013	sorgh 2011	sorgh %
	300	280	7	1300	1350	-3.7	0	0	0
	360	300	20	1100	900	22.2	1300	700	85.7
	350	300	17	1000	800	25	0	0	0
	0	0	0	0	0	0	700	710	-1.4
	340	300	10	1250	1100	13.6	730	700	4.3

<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>
</TBODY>

 

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