Hello i was wondering if i could get some help with a count if/vlookup forumula.
i was able to figure out total but i couldnt wrap my head around how many times it matches in the DATA column.
Thanks in advance!
Sheet Name = "DETAIL"
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="width: 74"]DIRECTION[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DATA[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MONTHS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]north
[/TD]
[TD]1[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]north[/TD]
[TD]2[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]north[/TD]
[TD]3[/TD]
[TD]FEB[/TD]
[/TR]
[TR]
[TD]south[/TD]
[TD]4[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]east[/TD]
[TD]5[/TD]
[TD]MAR[/TD]
[/TR]
[TR]
[TD]west[/TD]
[TD]6[/TD]
[TD]MAR[/TD]
[/TR]
</tbody>[/TABLE]
Sheet Name = "REPORT"
[TABLE="width: 500"]
<tbody>[TR]
[TD]NUMBERS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
- Need to look at each direction, first looking at "north" direction in the month of "JAN"
- Then need to look at the "NUMBERS" sheet and see how many times does that number matches the one that is in the DATA Column in DETAIL sheet.
i was able to figure out total but i couldnt wrap my head around how many times it matches in the DATA column.
Code:
=COUNTIFS(DETAIL!C:C,"JAN",DETAIL!A:A,"NORTH")
Sheet Name = "DETAIL"
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD="width: 74"]DIRECTION[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DATA[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]MONTHS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]north
[/TD]
[TD]1[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]north[/TD]
[TD]2[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]north[/TD]
[TD]3[/TD]
[TD]FEB[/TD]
[/TR]
[TR]
[TD]south[/TD]
[TD]4[/TD]
[TD]JAN[/TD]
[/TR]
[TR]
[TD]east[/TD]
[TD]5[/TD]
[TD]MAR[/TD]
[/TR]
[TR]
[TD]west[/TD]
[TD]6[/TD]
[TD]MAR[/TD]
[/TR]
</tbody>[/TABLE]
Sheet Name = "REPORT"
[TABLE="width: 500"]
<tbody>[TR]
[TD]NUMBERS[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]