Using the IF function to recreate a whole row

[HHML1]

Hi, I've tried to make this as clear as I can:

(link if the image doesn't work> https://i.imgur.com/Hs2qPg7.png)

Riiiight, so here's what I need to do.
I need to keep a running total of the performance of our salespeople (the ones in the B column) - How many leads they've been assigned, how many of those variables they have against them and how many sales they have generated.

The stats need to be 'live' as the list of customers is added to this spreadsheet all day.

It's easy enought to use the COUNTIF function to work out how many leads each salesperson has been assigned to, but I cannot work out how to fill out the rest of the values.

Is it possible to tell Excel, for example, if column B features "Kevin" to add up all of the sales in those "Kevin" rows in the F column?
I've toyed with text filters to only show the rows featuring "Kevin" rows, the COUNTIF function doesnt work with filtered data.

Is it possible to apply a formula in a new worksheet that says if B2 contains "Kevin", to print the whole row into this new worksheet?
That way I can use all the functions I need to generate the real time stats I need to report on.

Any help would be appreciated!
Sorry If I'm not 100% clear.

Thanks.

 

[FormR]

Hi, it sounds like you want SUMIFS() or COUNTIFS()

For example, this sums column F where column B = Kevin and column D = Yes.

Excel 2013/2016
	B	C	D	E	F	G	H
1	Name		Variable	Variable 2	Sales
2	Kevin		Yes	Yes	100		150
3	Tom		Yes	Yes	250
4	Kevin		No	No	0
5	Tom		No	Yes	0
6	Susan		Yes	Yes	125
7	Tom		No	Yes	0
8	Susan		No	No	0
9	Kevin		Yes	Yes	50
Sheet1

Cell Formulas
Range	Formula
H2	=SUMIFS(F:F,B:B,"Kevin",D:D,"Yes")

 

[HHML1]

Hi, it sounds like you want SUMIFS() or COUNTIFS()

For example, this sums column F where column B = Kevin and column D = Yes.

Excel 2013/2016

	B	C	D	E	F	G	H
Name	Variable	Variable 2	Sales
Kevin	Yes	Yes
Tom	Yes	Yes
Kevin	No	No
Tom	No	Yes
Susan	Yes	Yes
Tom	No	Yes
Susan	No	No
Kevin	Yes	Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]150[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"]125[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=SUMIFS(F:F,B:B,"Kevin",D:D,"Yes")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Ah!

I've never used COUNTIFS or SUMIFS. That's exactly what I was looking for.

I've just played around a bit and I think I can build exactly what I need with them!
Thank you!