sumproduct or countA, but how :)

[zyuth]

Fruits	Years
apple	2018
apple	2018
orange	2018
banana	2018
peach	2019
grape	2019
apple	2019
melon	2019
2018	3

<colgroup><col span="2"></colgroup><tbody>
</tbody>

hi i got this case, can you help me out for the formula? as u can see the answer is 3, but when i change to 2019 the answer is 4. thanks

 

[Michael M]

What formula are you using to get this result, and what is the criteria for the calculation

Excel 2013/2016
	A	B
1	Fruits	Years
2	apple	2018
3	apple	2018
4	orange	2018
5	banana	2018
6	peach	2019
7	grape	2019
8	apple	2019
9	melon	2019
10
11	2019	1
Sheet2

Cell Formulas
Range	Formula
B11	=SUMPRODUCT(--(A2:A9="apple"),--(B2:B9=A11))

 

[zyuth]

oh.. you are too specific by putting apple on it. what i want is how much fruit type in 2018 (or base on year that i will put)

 

[Michael M]

Excel 2013/2016
	A	B
1	Fruits	Years
2	apple	2018
3	apple	2018
4	orange	2018
5	banana	2018
6	peach	2019
7	grape	2019
8	apple	2019
9	melon	2019
10
11	2019	4
Sheet2

Cell Formulas
Range	Formula
B11	=COUNTIF(B2:B9,A11)

 

[zyuth]

to be truth i like your first answer, cause right now is only count for year only. beside the answer should be (2018=3) and (2019=4)

 

[strooman]

beside the answer should be (2018=3) and (2019=4)

Perhaps something like this?

B11 =SUMPRODUCT((B$2:B$9=$A11)/(COUNTIFS(B$2:B$9; B$2:B$9&""; A$2:A$9; A$2:A$9&"")+(B$2:B$9<>$A11)))
B12 =SUMPRODUCT((B$2:B$9=$A12)/(COUNTIFS(B$2:B$9; B$2:B$9&""; A$2:A$9; A$2:A$9&"")+(B$2:B$9<>$A12)))

	A​	B​
1​	Fruits​	Years​
2​	apple​	2018​
3​	apple​	2018​
4​	orange​	2018​
5​	banana​	2018​
6​	peach​	2019​
7​	grape​	2019​
8​	apple​	2019​
9​	melon​	2019​
10​	​	​
11​	2018​	3​
12​	2019​	4​

Sheet: Sheet1

 

[Besserwisser]

Hi,

easier is:

=SUMPRODUCT(($B$2:$B$9=A11)/COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,$A$2:$A$9))



Greetings



Christian

 

[Michael M]

Well, you'd probably get a better answer if you provided more information on what you require....

 

[strooman]

Hi,
easier is:
=SUMPRODUCT(($B$2:$B$9=A11)/COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,$A$2:$A$9))

Gives an error when there are blanks.

 

[zyuth]

oh... WOW!!! Thanks a lot All