Need formula to calculate margin based on type of lead source

[nickoli]

I have a spreadsheet for tracking deals and margins. I want to get the average margin per type of lead source.

I have one column, V, that lists the lead source type. There are three different text options used here. For simplicity sake I will say; ant, bat, car
I have another column, P, that has the margin calculated from the deal using data in other cells from other columns.

I am trying to create a formula for a separate cell that would figure, if column V is "ant" then include that particular row's P money value in the formula. Want to SUM all the P (say P2:P24) column values that are from "ant" lead source in column V and leave the others out, then divide that total by the overall deals from that lead source.

I already have totals for the lead source to reference for dividing:
In column V, already have this on row 25:
=COUNTIF(V2:V24,"ant")
which gives me my total deals from that lead type. These cell are V25, V26, V27 for the 3 different lead sources. So these are the reference for once I have the sum of all P values for one lead type, i can divide by these cells, and figure that should give me my total margin based only on that lead source.

Please help advise me on the right formula to create for this. Appreciate any input.

[Jarvin24]

Hi nick,

Appreciate if you could upload a template.

Tnx

[nickoli]

Thanks for the reply Jarvin, but I solved it myself with a bit more searching. SUMIF function is key.

Here is what I used for that cell in case it helps anyone else out in the future:

=SUMIF(V2:V24, "ant", P2:P24)

SUMIF(range, criteria, [sum_range])

worked just how I needed it to

[salamander20]

Attaching an example of a pivot table. Is this what you were looking for and would this work for you?

[nickoli]

Thanks Salamander, but I did get it figured out.

One note I might add from my last post, I did need to include the division to the end as well, so it divides and gets an average instead of just a total sum.

=SUMIF(V2:V24, "ant", P2:P24)/V25

Which V25 was my reference for total per source type - COUNTIF mentioned in 1st post

[Khalidngo]

Hi nickoli,

You have the solution FWIW, if you want a single formula solution, try:
=AVERAGEIF(V2:V24,"ant",P2:P24)

Or this if you dont want to consider zeros:
=AVERAGEIFS(P2:P24,P2:P24,">0",V2:V24,"ant")

Blessing