Payroll - Combine SUMPRODUCT, SUMIF, COUNTIF within date range

[niceguyty]

The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.

I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.

=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))

Attached is what I am talking about. The columns I need help with are yellow.

I could find anything like this by searching.

THANK YOU!

[MSP77079]

Not so tricky. You are already using this formula.

=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))

If you understand how that one works, then you should know to modify it as follows:
=SUMPRODUCT(--(Master!$A$1:$A$175=Totals!$A3),(Master!$K$1:$K$175),--(Master!$B$1:$B$175>=$E$1),--(Master!$B$1:$B$175<=$G$1))

For the first one,
=COUNTIF(Master!A1:A176,PayPeriod!A4)
I would first convert it to a SUMPRODUCT as well, just for practice:
=SUMPRODUCT(--(Master!$A$1:$A$175=$A3))
Then, you only need to add the ending to it like the one above.
=SUMPRODUCT(--(Master!$A$1:$A$175=$A3),--(Master!$B$1:$B$175>=$E$1),--(Master!$B$1:$B$175<=$G$1))

Maybe you inherited the workbook and they never explained to you how it works? If you would like an explanation of how these SUMPRODUCT formulas apply their magic, let us know.

[niceguyty]

Thank you for your help. It was fun to past in the formulas and see the magic as you said. I get the general idea of how it works but I am missing the something important. I know the last part of the formula defines the range of dates.

Your formulas worked beautifully for payperiod columns C & D but I cannot get it to work in E. I tried adding and modifying the initial service column in the same ways and got the wrong results.


I also have to figure out how calculate the percentage of "Y" compared to "N" in the autopay column of worksheet "totals" and "payperiod" (payperiod being defined in the same date range as the formulas I am working on.

I have searched this but am still clueless. I really appriciate your willingness to help.

Attached is the latest draft with the problems areas in yellow.

[Carim]

Hi,

Attached is your worksheet with the correct sumproduct() functions ...

[MSP77079]

Hi guy,

Looks like Carim was kind enough to supply the answers. I will supply the explanation. I will use the easiest one for the explanation ...

Please Login or Register  to view this content.

You can either follow along mentally or actually put it into your workbook.

I am using the file “Payroll G.xls”. The first thing I will do is to calculate the results for cell C3 on the PayPeriod sheet. I will do this by using 4 blanks columns on the Master sheet, columns M, N, O, and P

In cell M3, I put this formula:
=A3=PayPeriod!$A$3
and drag it down. This compares each of the sales reps initials with the initials in cell A3 on the PayPeriod sheet and returns a series of TRUE and FALSE.

To convert these into numeric results, we multiply by 1:
=(A3=PayPeriod!$A$3)*1
Multiplying TRUE by 1 gives 1; multiplying FALSE by 1 gives 0.

For debatable reasons, people prefer to use this instead of multiplying by 1:
=--(A3=PayPeriod!$A$3)

Using similar logic, I put the following formulas in cells N3 and O3
=--(B3>=PayPeriod!$E$1)
=--(B3<=PayPeriod!$G$1)
and drag them down. These formulas compare the dates in column B with the pay period start and end dates, and return TRUE or FALSE, then multiply the result by 1 (or by -1 twice) to get numeric results of 1 and 0.

We want to identify those rows which meet all 3 criteria:
1. sales rep’s initials match the initials in PayPeriod cell A3
2. date of sale is on or after the beginning of the PayPeriod
3. date of sales is on or before the end of the PayPeriod

To determine which rows meet all 3 criteria, we can multiply together the results for each of the 3 criteria, by putting this formula in cell P3:
=M3*N3*O3
and dragging down.

To count the number of rows where all 3 criteria are met, we sum up column P. I put this formula in cell M2:
=SUM(P:P)

There is an obvious problem with using this approach. It required 4 columns to get the results for a single cell. So, how to simplify this?

First, I can replace the formula in cell M2 with SUMPRODUCT:
=SUMPRODUCT(M3:M28,N3:N28,O3:O28)

The function SUMPRODUCT does the combination of what we did in column P, and it also does the SUM we used in cell M2. Namely, it multiplies M3*N3*O3, and adds that to M4*N4*O4, et cetera.

The next thing we can do is replace each of these cell references with the formulas that produced them. Doing this one at a time, we get:
=SUMPRODUCT(M3:M28,N3:N28,O3:O28)
=SUMPRODUCT(--(A3:A28=PayPeriod!$A$3),N3:N28,O3:O28)
=SUMPRODUCT(--(A3:A28=PayPeriod!$A$3),--(B3:B28>=PayPeriod!$E$1),O3:O28)
=SUMPRODUCT(--(A3:A28=PayPeriod!$A$3),--(B3:B28>=PayPeriod!$E$1),--(B3:B28<=PayPeriod!$G$1))

To be able to take this formula and put it on the PayPeriod sheet (and be able to drag it down that sheet’s column C without a problem) we need to change the references.

=SUMPRODUCT(--(Master!$A$3:$A$28=PayPeriod!$A3),--(Master!$B$3:$B$28>=PayPeriod!$E$1),--(Master!$B$3:$B$28<=PayPeriod!$G$1))

[Carim]

@ MSP77079

You are extremely kind to go through such a step-by-step explanation ...
This is worth much more than the lazy copy-paste I did ...

Cheers
Carim

[niceguyty]

You guys are too excel savvy for my little mind. It amazes me to see how easy you make it seem. Thanks Carim and MS.

I would offer to help you with something but you already know how to use autosum.

[Carim]

Hi,

I am sure MSP77079 is as happy as I am to see your problem is solved, and that you have increased your own knowledge, in the process ...

Thanks fot the feedback

[niceguyty]

I was afraid you generous people were getting tired of helping me, a newbie, but after over a week of research and failed attemps, I basically got nowhere.

I need a formula in column K that will calculate the projected total summer income from the "master" column J. The problem I am having is that we work Saturdays but not Sundays. The date schedule is in the "info" worksheet.

If you are really bored I would LOVE your help. The explinations are in the yellow cells. If you are feeling extra generouse, feel free to check out my yellow comments on the "earthworks" worksheet.

I love you all.

THANK you!

[Carim]

Hi,

In order to calculate work days including Saturdays, use following formula

Please Login or Register  to view this content.

[niceguyty]

Carim,

You are brilliant! With your help I was able to finish the entire project.

Thanks again,

Ty

[Carim]

Ty,

To be a nice guy ... Thanks to everyone who contributed ...

Thanks for the feedback