sumproduct or countA, but how :)

[zyuth]

[TABLE="width: 154"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Fruits[/TD]
[TD]Years[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD]peach[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]melon[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2018[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

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

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#0070C0]

Fruits​

[/td][td=bgcolor:#0070C0]

Years​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

apple​

[/td][td]

2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

apple​

[/td][td]

2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

orange​

[/td][td]

2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

banana​

[/td][td]

2018​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

peach​

[/td][td]

2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

grape​

[/td][td]

2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

apple​

[/td][td]

2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

melon​

[/td][td]

2019​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td=bgcolor:#FFFF00]

2018​

[/td][td=bgcolor:#D2ECB6]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td=bgcolor:#FFFF00]

2019​

[/td][td=bgcolor:#D2ECB6]

4​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[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