Count if formula by two sheets

[asyamonique]

Hello,
Good Day,
My main datas are located on Sheet1 Columns A to AH all the way down.
Column A has name of the months.Column D is the target datas.

I want to put a formula on the Sheet3 cell A6 which will lookup on the same Sheet to cell B1 which has the name of the mounth,
will check column D on Sheet1 and will count enuqie datas(example Apple) related with same month.
Many Thanks

 

[Aladin Akyurek]

In A6 of Sheet3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Sheet1!$D$2:$D$200=""),IF(Sheet1!$A$2:$A$200=B$1,MATCH(Sheet1!$D$2:$D$200,Sheet1!$D$2:$D$200,0))),
    ROW(Sheet1!$D$2:$D$200)-ROW(Sheet1!$D$2)+1),1))

 

[asyamonique]

Hi Aladin,
Thanks for helping me out,
Could you please check the tables given below what if I'm going to count the duplicates in order linked cells.


SHEET 1 columnA

[TABLE="width: 76"]
<tbody>[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]January[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]February[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
[TR]
[TD]March[/TD]
[/TR]
</tbody>[/TABLE]


Column D

[TABLE="width: 38"]
<tbody>[TR]
[TD]16[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]24[/TD]
[/TR]
[TR]
[TD]14[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]16[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]18[/TD]
[/TR]
[TR]
[TD]8[/TD]
[/TR]
[TR]
[TD]14[/TD]
[/TR]
[TR]
[TD]14[/TD]
[/TR]
[TR]
[TD]14[/TD]
[/TR]
[TR]
[TD]20[/TD]
[/TR]
[TR]
[TD]24[/TD]
[/TR]
[TR]
[TD]24[/TD]
[/TR]
[TR]
[TD]6[/TD]
[/TR]
[TR]
[TD]14[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]20[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]18[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 3

Cell B1 = Month
Cell C1 = 6(Sheet1 column D values)
Cell A6 = Formula needed!(Counting the duplicates base on b1 & c1 values which matches on column Sheet1 column A&D, if B1 vale is January and C1 value is 6 then A6 result will be as "2" )

Many Thanks,

 

[Aladin Akyurek]

Which month do we have in B1?

 

[Aladin Akyurek]

Which month do we have in B1?

Sorry, I missed your B1 = January specification.

The question is not a unique count issue as I thought it was, it's rather just a conditional count. Thus:

In A6 of Sheet3 just enter:

=COUNTIFS(Sheet1!D:D,$C1,Sheet1!A:A,$B1)

This will return 2 as result.

 

[asyamonique]

Thanks a lot,
Its ok now

 

[Aladin Akyurek]

Thanks a lot,
Its ok now

You are welcome.