Counting a sub category within a larger category

[sbarrett]

I have a data set where I am recording information for individual items.  Each item has a lot number and a severity level (A, B, N/A).  I would like to calculate a third column to trend the total number severity B item per lot as well as a forth column to trend the total number of severity A items per lot.  Is there some kind of for loop that I can use in combination with an if statement to count the number of severity a and b items per lot?

Example of my data set: 

Lot Number	Severity	Total Sev B per lot	Total Sev A per lot
A40199	N/A
A40200	N/A
A40201	N/A
A40205	N/A
A40206	A
A40206	B
A40207	B
A40207	B
A40208	B
A40208	B
A40209	B
A40209	B
A40210	N/A

Example of how i want my data set:

Lot Number	Severity	Tota Sev B per lot	Total Sev A per lot
A40199	N/A	0	0
A40200	N/A	0	0
A40201	N/A	0	0
A40205	N/A	0	0
A40206	A	1	1
A40206	B	1	1
A40207	B	2	0
A40207	B	2	0
A40208	B	2	0
A40208	B	2	0
A40209	B	2	0
A40209	B	2	0
A40210	N/A	0	0

[JohnJPS · Accepted solution]

Hi @sbarrett,

One easy way is to Cross-Tab, then Multi-Field formula on the specific fields of interest, then join back to the original data set; (see attached).

[sbarrett]

This worked! I appreciate your help, thank you.