ExcelBanter - Count value in range, but exclude based on adjacent value.

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Count value in range, but exclude based on adjacent value. (https://www.excelbanter.com/excel-worksheet-functions/447591-count-value-range-but-exclude-based-adjacent-value.html)

amacrab

Count value in range, but exclude based on adjacent value.

Hi.

In the attached spreadsheet, is there any way to count the number of times "A" appears in column B, but exclude the times it appears next to "saf" in column A? So the answer should be 3, but how do you get there using a formula?

Thanks.

amacrab

1 Attachment(s)

Quote:

Originally Posted by amacrab (Post 1607160)

Hi.

In the attached spreadsheet, is there any way to count the number of times "A" appears in column B, but exclude the times it appears next to "saf" in column A?

Thanks.

Attachment added

Spencer101

1 Attachment(s)

Quote:

Originally Posted by amacrab (Post 1607161)

Attachment added

Have a look at the attached. I've included a SUMPRODUCT and a SUMIFS approach for this. The SUMIFS one will only work in Excel 2007 or later.

amacrab

Quote:

Originally Posted by Spencer101 (Post 1607162)

Have a look at the attached. I've included a SUMPRODUCT and a SUMIFS approach for this. The SUMIFS one will only work in Excel 2007 or later.

Always amazed at how much excellent help you can get on these forums. Thank you!

Spencer101

Quote:

Originally Posted by amacrab (Post 1607163)

Always amazed at how much excellent help you can get on these forums. Thank you!

The pleasure is all mine :)

All times are GMT +1. The time now is 10:01 PM.