Count and list values in a column if certain conditions are met

Hello,

My apologies if this is the wrong place to post but I am trying to find out if and how I can extract more data than just by using countifs.

I have just learned countifs and I think it?s great. I want to make lists based on several colums and I can?t make it work with the formulas I know of as of know.

For example, if A is sales rep, B is name of customer and C is order status, is there a way to count AND present the name of each unique value in list form?

I want to not only count the sales of a rep but also list the name of the businesses he has sold to. For example I only want to show pending sales from sales Rep 1, and list the name of all customers that has a pending sale sold by Sales rep 1. I know how to count this but I also need a list that displays the name of each customer, not just the number of sales of a given type or by a given rep. Another example is to show all orders from Sales rep 1 in June and list the value of each sale in that category in a list. Hope this makes sense.

I have tried using AI but it?s not really working. I can do what I want today combining SUM, COUNTIFS and manual typing but I want to fully automate it.

Appreciate any suggestions.

Edit: My bad, I have attached a very easy example. Had to make it now so just made it super simple to illustrate what I want.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Where's the attachment?

LoL. It's too simple.

WHAT do you expect to see as the end result?

WHERE is the data coming from?

OK I am sorry Glenn, just made it on the spot.

The data is a massive exported Excel-list from a CRM system with hundreds of lines of sales from a number of sales reps, of various value and to a number of unique customers.

My goal was simply to show what I wanted to create in the end.

IF sales rep = "x", AND sales status = "sold", I want to show that in a list (not using countifs, but displaying each company that has a sold order from sales rep x).

My issue is that I can count the number of "sold" from rep "x", but not display them in a list. Today I have to manually name each company in various lists.

I want to be able to say "list the name of every company that has a lost order in which sales rep 1 was the seller".

For example: B15 in sheet "Andy" says "Apple" because I manually entered it. I want that cell to be filled in automatically.

Please upload a morecrealistoc sample, clearly showing what you expect to see and where you expect to see it.

YES! We are good, but we are not clairyoyant.

The problem is I cannot use any real data so I have to make stuff up which takes a long time.

So does anyone sharing data here. I shall take another look.

OK - had a look.

What is source data and what is what you are trying to achieve? Some signposting would help, please.

OK. Let's start with this drag copied across (remove ALL data first):

=LET(f,FILTER('Ark1'!$B$6:$B$48,MONTH('Ark1'!$E$6:$E$48)=MONTH(DATEVALUE(B$3&" 1"))),c,COUNTA(f),IFERROR(VSTACK(f,"Total: "&c),""))

Correction:

=LET(f,FILTER('Ark1'!$B$6:$B$48,(MONTH('Ark1'!$E$6:$E$48)=MONTH(DATEVALUE(B$3&" 1")))*('Ark1'!$A$6:$A$48=$A$1)),c,COUNTA(f),IFERROR(VSTACK(f,"Total: "&c),""))

Then in B16 copied across:

=LET(f,FILTER('Ark1'!$B$6:$B$48,('Ark1'!$C$6:$C$48=B$15)*('Ark1'!$A$6:$A$48=$A$1)),c,COUNTA(f),IFERROR(VSTACK(f,"Total: "&c),""))

Really not sure where you are getting the averages from.

Open the workbook I attached!

You will find the formulae correct for your locale when you open it.

It works in the attachment I provided - did you open the correct attachment?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B	C	D
15	Sold	Lost	Pending
16	Walgreens Boots Alliance	Intel	General Electric
17	Total: 1	Pfizer	Kroger
18		Total: 2	Citigroup
19			Total: 3

Sheet: AB

It is language, but as you haven't said what your exact locale is, I cannot provide you with a translation.

Open the SECOND attachment I provided in post #16.

Yes I have opened the most recent version.

My locale is Norway but I changed language to English in Excel now.

That beautiful table you posted I cannot see at all.

That should not be necessary, in fact it might make things worse.

=LET(f;FILTRER('Ark1'!$B$6:$B$48;(MÅNED('Ark1'!$E$6:$E$48)=MÅNED(DATOVERDI(B$3&" 1")))*('Ark1'!$A$6:$A$48=$B$1));c;ANTALLA(f);HVISFEIL(VSTACK(f;"Total: "&c);""))

=LET(f;FILTRER('Ark1'!$B$6:$B$48;('Ark1'!$C$6:$C$48=B$15)*('Ark1'!$A$6:$A$48=$B$1));c;ANTALLA(f);HVISFEIL(VSTACK(f;"Total: "&c);""))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

That beautiful table you posted I cannot see at all.

It is in the attachment to post #16. Attached again here.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Thank you so much. Been looking at it now and trying to edit it to fit my real data.

Your skills are very impressive