Counting in Multiple Columns

[omnomster]

Hello everyone!

I'm not quite sure if my issue is unique or not, but here goes!

I'm trying to look down a column and find a result (either "Y" or "N") and then look at the cell next to it to see if the last character is a letter ("A", "B", "C", "N"). If those criteria are met, I would like it to return a total count of those items.

This is what I have so far:

Certified	Stock Number
Y	P1864A
Y	P1823B
N	14328N
N	P1745

Please Login or Register  to view this content.

So far, I have a SUMPRODUCT able to tell me if it meets "Y" in column A AND "A" on the right side of column B.

Is there a condensed way to include other letters in the right hand search without duplicating this formula over and over? To make it even more complicated, I have these types of tables across the spreadsheet multiple times for other models of product, so making it as condensed of an equation would be very helpful if it would ever need modification.

Thank you so much in advance!

[Tony Valko]

Do you mean something like this:

RIGHT(range) = A or B or C or D

[omnomster]

Yes. Essentially, I want the equation to return a result if there is any letter on the right, depending on the "Y" or "N" of the certified column.

Thank you for your reply!

[Tony Valko]

Maybe this...

Data Range

	A	B	C	D	E
1	Certified	Stock Number		Yes/No	Count
2	Y	P1864A		Y	2
3	Y	P1823B		N	1
4	N	14328N
5	N	P1745
6	------	------	------	------	------

This formula entered in E2 and copied down:

=SUMPRODUCT(--(A$2:A$5=D2),--ISERR(-RIGHT(B$2:B$5)))

[omnomster]

Thank you so much, Tony!! It works perfectly!! :D

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[omnomster]

Thank you for telling me where to mark it as solved. I was hunting around for it.

Thank you again!!