A couple of problems with google sheets & countifs - please help!

Hi there! Hope you're all having a nice Sunday.

I have a couple of issues that I'd really appreciate some help on.

1) Firstly, I'm trying to count each value if in the first image column (Deal Stage) is Lost. Obviously there are a couple of different Lost's that come up. Such as "Lost - Rejected" or "Lost - To competition". In my formula, =COUNTIFS('M&A HS Export Pull'!$B:$B,"*Lost*",'M&A HS Export Pull'!$C:$C,">="&D$2,'M&A HS Export Pull'!$C:$C,"<"&E$2,'M&A HS Export Pull'!$C:$C,"<>"), will this count all the different incidences of Lost (Lost, Lost - Rejected, Lost - To competition)? Or is there another way I need to do this, like an and?

Attachment 782935

2) Secondly, I want to then count the times any Lost value appears (column B) when it is in the lead pipeline (column G) if a seller meeting has been booked (column C).

help.png

Formula is above!

Thanks in advance!

I guess it can be done with a pivot table.

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Hi The Protector,

Now that your post count has reached "10", you should find that you are now able to post links.

Please post a link to a copy of your file, with anonymous but realistic data, that has enabled Editor for Anyone with the link.

Then we can look further with your enquiry.

I am not interested in opening those links.

You can add the file also on this forum.


HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

for future reference, when providing representative sample data, make sure that it minimises the need to fiddle with formulas all over the place by ensuring that it will provide some kind of valid result.

for example, your data is for up to year 2021, but your search criteria is dates in 2022.

continuing on....

for the Deal formula in D11, the final criteria is redundant, also, as you want Deal, there is no word before or after so the * wildcards are redundant, and so you can simplify to:

Please Login or Register  to view this content.

for Lead Pipeline in D12, same issues as D11:

Please Login or Register  to view this content.

for Lost any in D13, here you can utilise the * wildcard:

Please Login or Register  to view this content.

for Blacklist in D14, same as for D13:

Please Login or Register  to view this content.

for Lead in D10, im unsure the meaning of your notes in B10, so i have followed the same structure as for D12:

Please Login or Register  to view this content.