Count Values in 2 Columns return unique for row.

LMCT3

New Member
Joined
Jul 17, 2019
Messages
8
Hi,

Thanks in advance for any assistance.

=COUNTIFS(BookingsYTD!A:A,"*"&YTD!A15&"*")+COUNTIFS(BookingsYTD!L:L,"*"&YTD!A15&"*")

Trying to count "*"&YTD!A15&"*"

In Columns A:A and L:L.

This formula counts both and returns the value 2 if both columns contain that string.

The problem is in the records it could be either in A:A OR L:L OR Both. In the case that it is both, how would I return values unique to the row.

So if records list had those strings as

A:A L:L
TRUE FALSE
FALSE TRUE
TRUE TRUE

That the resulting figure would be 3 where there are 3 unique rows where any of those return TRUE.

Hopefully my explanation is sufficient. Thanks for your time everyone.

Regards,
Lewis.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,113
Office Version
  1. 2016
May be:

Code:
=COUNTIF(BookingsYTD!A:A,"*"&YTD!A15&"*")+COUNTIF(BookingsYTD!L:L,"*"&YTD!A15&"*")-COUNTIFS(BookingsYTD!A:A,"*"&YTD!A15&"*",BookingsYTD!L:L,"*"&YTD!A15&"*")
 
Solution

LMCT3

New Member
Joined
Jul 17, 2019
Messages
8
@Bebo

Thankyou very much that's perfect, it didn't occur to me to count each column add them, then subtract the IFS. Perfection in simplicity.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,844
Messages
5,833,935
Members
430,246
Latest member
Johny V

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top