Count the same numerical occurence in 2 separate columns

[jr100]

Hello,

Any help would be greatly appreciated.

I'm trying to count the number of occurences of a number in on column and then a y or n in the second column relating to that number. IE Column A has as series of numbers that run from 1 to 4. Column B has either a Y or N.

Example: Column A has 1 in cell one and Column B has Y in cell one and I'm trying to count the number of times this happens in the column.

I have tried a bunch of different things, countif, sumif, Dcount A, sumproduct, etc. and none of them seem to work. Any ideas?

Thank you for your help!

Have a great day.

[VBA Noob]

=SUMPRODUCT(--($A$1:$A$5000=1)*(--($B$1:$B$5000="Y")))

Change the 1 and Y to what ever combo

More help on sumproduct can be find on attached link

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

[Ragdyer]

Try this:

=Sumproduct((A1:A1000=1)*(B1:B100="Y")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"jr100" <[email protected]> wrote in
message news:[email protected]...
>
> Hello,
>
> Any help would be greatly appreciated.
>
> I'm trying to count the number of occurences of a number in on column
> and then a y or n in the second column relating to that number. IE
> Column A has as series of numbers that run from 1 to 4. Column B has
> either a Y or N.
>
> Example: Column A has 1 in cell one and Column B has Y in cell one and
> I'm trying to count the number of times this happens in the column.
>
> I have tried a bunch of different things, countif, sumif, Dcount A,
> sumproduct, etc. and none of them seem to work. Any ideas?
>
> Thank you for your help!
>
> Have a great day.
>
>
> --
> jr100
> ------------------------------------------------------------------------
> jr100's Profile:
> http://www.excelforum.com/member.php...o&userid=31335
> View this thread: http://www.excelforum.com/showthread...hreadid=564879
>

[Tom Hutchins]

A pivot table would be an easy way to do this. Your data needs headings for
this. Click a cell in your data, then select PivotTable and PivotChart Report
from the Data menu. Your data is a Microsoft Office Excel list, and you want
to create a PivotTable. Click Next. Make sure Excel has selected the correct
range, then click Next. Select where you want to put the pitvot table, then
click Finish. Add the column A field (the numbers) to the Row Area. Then, add
the column B field (Y or N) to the Row Area. Finally, add the column B field
to the Data Area. By default, the pivot table will count the data items.

Hope this helps,

Hutch

"jr100" wrote:

>
> Hello,
>
> Any help would be greatly appreciated.
>
> I'm trying to count the number of occurences of a number in on column
> and then a y or n in the second column relating to that number. IE
> Column A has as series of numbers that run from 1 to 4. Column B has
> either a Y or N.
>
> Example: Column A has 1 in cell one and Column B has Y in cell one and
> I'm trying to count the number of times this happens in the column.
>
> I have tried a bunch of different things, countif, sumif, Dcount A,
> sumproduct, etc. and none of them seem to work. Any ideas?
>
> Thank you for your help!
>
> Have a great day.
>
>
> --
> jr100
> ------------------------------------------------------------------------
> jr100's Profile: http://www.excelforum.com/member.php...o&userid=31335
> View this thread: http://www.excelforum.com/showthread...hreadid=564879
>
>

[jr100]

Thank you for the help. Both forumula's work awesome.

Have a great day!

[RagDyeR]

WE appreciate the feed-back.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"jr100" <[email protected]> wrote in
message news:[email protected]...

Thank you for the help. Both forumula's work awesome.

Have a great day!


--
jr100
------------------------------------------------------------------------
jr100's Profile:
http://www.excelforum.com/member.php...o&userid=31335
View this thread: http://www.excelforum.com/showthread...hreadid=564879