Fomula help

[Nataliesg2]

I am trying to find a formula that would work out if p1 is blank but l1 says "sent" it counts it but when p1 says 'yes' it shouldn't count it in the list.

Not sure if I'm making any sense! Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

Your question is a bit incomplete. Count it where? It what list?
Maybe it would be helpful in your description to show us a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Nataliesg2]

Welcome to the Board!

Your question is a bit incomplete. Count it where? It what list?
Maybe it would be helpful in your description to show us a sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hiya,
Sorry I cannot download the XL2BB as I'm at work and cannot download things so I have put a screen shot instead.

So.... I am trying to get an automatic count of people awaiting appointments, so if the merged cells (JKL) say 'Sent to MAS' and cell Q does not have a date in it I would like there to be the amount of numbers in the blue list of 'referrals sent awaiting appt' rather than me counting the 100's of people I sent last year. I'm not even sure there is a formula for this but it is worth asking.

Thanks

 

[Joe4]

So, are you trying to count records where the entry in you J-K-L column starts with "Sent...", but column P does NOT say "Yes"?
Is that correct?

Also, I HIGHLY advise you NOT to use merged cells, EVER!
They are probably the single worst feature of Excel and cause all sorts of issues, for things like Sorting, Filters, VBA, etc.

When trying to center across multiple columns in a single row, it is much better to use the Center Across Selection formatting option.
This will give you the exact same visual effect as merged cells across rows, but without all the issues that merged cells cause.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[Nataliesg2]

So, are you trying to count records where the entry in you J-K-L column starts with "Sent...", but column P does NOT say "Yes"?
Is that correct?

Also, I HIGHLY advise you NOT to use merged cells, EVER!
They are probably the single worst feature of Excel and cause all sorts of issues, for things like Sorting, Filters, VBA, etc.

When trying to center across multiple columns in a single row, it is much better to use the Center Across Selection formatting option.
This will give you the exact same visual effect as merged cells across rows, but without all the issues that merged cells cause.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Sorry I've confused things and couldn't edit my question once I posted. Ignore cell p I want to count cell J-K-L column if it states "Sent..." but column Q does not have a date in it.

 

[Joe4]

Sorry I've confused things and couldn't edit my question once I posted. Ignore cell p I want to count cell J-K-L column if it states "Sent..." but column Q does not have a date in it.

If you fixed your merged cells to use the Center Across Selection formatting option like I advised, so your entry is actually in column J, then this formula should get what you want:

=SUMPRODUCT(--(LEFT(J2:J22,4)="Sent"),--(Q2:Q22=""))

 

[Skyybot]

Sorry I've confused things and couldn't edit my question once I posted. Ignore cell p I want to count cell J-K-L column if it states "Sent..." but column Q does not have a date in it.

Did you get a solution to your problem yet?