How to put countif function into array formula?

[dunganbrendan]

Good afternoon all.

We are trying to count and sum the monthly payroll for all employees #1,#2, etc. up to a certain count (in the example below, 7). Essentially, we need all the Boolean answers (see formula) to be in a sequence and then for an array formula to spit out a sum (and count, though this may seem redundant) of all the TRUE monthly payroll figures. (Note: The Employee column is redundant in this case--this is just to show a count.)

The Boolean Countif function displayed below takes the count from the first cell (absoluted) to the first cell (relative) and then from the first cell to each cell below it.

We are able to do this with helper columns, but they are making my template twice as heavy and will make it up to five times heavy if I create the template the way I want it. (The helper columns aren't only helper columns, but helper tables. And there would be perhaps ten unnecessary tables when the project is completed. Trying to keep it simple and at a reasonable size for customers.)


Limitations:

1. The data will always be formatted in a table.

2. The data is always sorted based on the hire date of the employee. This means there will be some gaps in the data (for example, we are summing the June payroll of the seven oldest employees and there was an old employee who retired in March of the same year). Because of this, Index/Match formulas are out of the picture. If there is a way to use Index/Match with this idea, I am open to it, but I would like to stay away from those formulas if possible.

3. Bonus/low priority: Because there are gaps in the data (example above), it would be ideal if we could combine two formulas, one asking for employees less than the criteria figure (again, 7 in our example below) and one asking for the figure that is not blank and is equal to the criteria.

4. Data from our customers can include up to a few thousand employees.

VBA code (with explanation please) would be very much appreciated as would any reference to online articles or other forums regarding countifs, array formulas, and anything related to the question at hand. Any help would be GREATLY appreciated. Thank you!!!!

(using Excel 2016)

 

[JamesCanale]

Is this what you want?

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H
1				employee	monthly payroll
2	Criteria: Countif/sumif total is less than or equal to:	7		1	3000		Sum	74050
3				1	45000		Count	7
4
5				1	2450
6				1	7200
7				1	3600
8
9				1	8900
10
11				1	3900
12				1	4200
13				1	7400
14				1	8400
15				1	5500
16				1	7770
Sheet13

Cell Formulas
Range	Formula
H2	H2	=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D2:D16,,,ROW(INDIRECT("1:"&ROWS(D2:D16))),1))<=B2),E2:E16)
H3	H3	=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D2:D16,,,ROW(INDIRECT("1:"&ROWS(D2:D16))),1))<=B2),--(D2:D16=1))

 

[dunganbrendan]

@JamesCanale This is greatly appreciated.

You are using the indirect function to reference cells/rows with "1"...is there any way you could reference cells greater than 0 instead of referencing the "1"? The employee column does not exist in the project I am working on. I will try to give you a better idea of what I am working on.

We review employment data from our customers and distribute benefits based on the new jobs and payroll they have created since a given date. With that, we need a summary for each data set that shows captures jobs that existed before the company applied for our program, the payroll of those jobs, the new jobs created since then, and the new payroll of those jobs. The input tab asks for hire date, termination date, and wages for a given period. I am displaying mock data. In this example, a company had five jobs before applying to our program and has since created nine jobs in Months 1-6, eight jobs in Months 7-12, and seven jobs at year's end.

The wage ($60,000 for each employee in this example) is "spread out" over each month the employee worked for a given reporting period (usually Jan. - Dec. but this can vary). So in the example the first employee worked Jan. - June and therefore we show that $10,000 was earned on average for each month worked. The shaded cells below are the "old" jobs that already existed before the company applied. The unshaded cells represent the new jobs for each month. I was trying to find a vba code that counts and sums for cells conditionally formatted a certain way but could not find one.

Right now I have helper tables that use if statements for each employee for each month. I would like to get rid of these tables with a formula for each month like the one you have written.

Thank you again James. I greatly appreciate your help. Perhaps I should have just started with the details I have just explained.

 

[JamesCanale]

I've read your response a few times, and I'm just not grasping it... Sometimes it's easier to show input, desired output, and any conditions, and let us have a go at it.

My formulas above take a running sum of the employee column and notes where is passes 7, and then sums up to that point in the wages column. It could be changed to 'count' instead of 'sum' if that would help. Also, there is VBA code that can check formatting (conditional formatting should have a 'condition' that could be reproduced in another function). I use a function like this for formatting:

Function intcolor(c As Range)

intcolor = c.Interior.Color
End Function

I use that in another function to check if the color is something that I want to treat differently.

 

[dunganbrendan]

I've read your response a few times, and I'm just not grasping it... Sometimes it's easier to show input, desired output, and any conditions, and let us have a go at it.

My formulas above take a running sum of the employee column and notes where is passes 7, and then sums up to that point in the wages column. It could be changed to 'count' instead of 'sum' if that would help. Also, there is VBA code that can check formatting (conditional formatting should have a 'condition' that could be reproduced in another function). I use a function like this for formatting:

Function intcolor(c As Range)

intcolor = c.Interior.Color
End Function

I use that in another function to check if the color is something that I want to treat differently.

James,

Forgive me for my ignorance. You, sir, are an absolute beast. I changed the subtotal function from sum to count and it works. It is crazy. Thank you so much for cracking this...and you didn't even have to use code. I am very grateful. Thank you.

Brendan

 

[JamesCanale]

That is kind of you to say. I'm sure all of us love doing this. Sometimes you have to use code, but mostly I find that having to use VBA is a small failure. It's often less interesting to me.

The more time you spend on Mrexcel the better you get. I was pretty handy with excel a year ago, but I think I doubled my effectiveness in the last eight months.

There are guys on here that can use one cell where others would have to use a whole worksheet. And the VBA code you pick up here is perfect in its economy.

 

[dunganbrendan]

James,

Would you recommend taking a VBA course? I was thinking about taking one but now I'm thinking I could just do some reading and figure things out as I go. Like you say, I'm sure spending time here and trying to create solutions helps a ton as well.

Also, the formulas are great and they work super well on my worksheet. One question with that--I think the volatile Offset function is slowing the spreadsheet. Is there any way to set the formula you had in an index formal or anything not volatile? For internal purposes it would be totally fine but I could see our customers complaining because of having a short delay every now and then. If not then no problem. Thanks again!

That is kind of you to say. I'm sure all of us love doing this. Sometimes you have to use code, but mostly I find that having to use VBA is a small failure. It's often less interesting to me.

The more time you spend on Mrexcel the better you get. I was pretty handy with excel a year ago, but I think I doubled my effectiveness in the last eight months.

There are guys on here that can use one cell where others would have to use a whole worksheet. And the VBA code you pick up here is perfect in its economy.

 

[dunganbrendan]

James,

Would you recommend taking a VBA course? I was thinking about taking one but now I'm thinking I could just do some reading and figure things out as I go. Like you say, I'm sure spending time here and trying to create solutions helps a ton as well.

Also, the formulas are great and they work super well on my worksheet. One question with that--I think the volatile Offset function is slowing the spreadsheet. Is there any way to set the formula you had in an index formal or anything not volatile? For internal purposes it would be totally fine but I could see our customers complaining because of having a short delay every now and then. Thanks again!

That is kind of you to say. I'm sure all of us love doing this. Sometimes you have to use code, but mostly I find that having to use VBA is a small failure. It's often less interesting to me.

The more time you spend on Mrexcel the better you get. I was pretty handy with excel a year ago, but I think I doubled my effectiveness in the last eight months.

There are guys on here that can use one cell where others would have to use a whole worksheet. And the VBA code you pick up here is perfect in its economy.

 

[JamesCanale]

I spent 20 years learning VBA via google and such. I'll bet a class would be much faster to get started. Then it is a matter to really getting skills by solving real problems (this place helps).

I'm not having much luck with other formulas for this...

 

[dunganbrendan]

Alright James. I really appreciate your insight. Just from getting familiar with Excel and VBA in the last couple months, I imagine it would be useful virtually anywhere in the working world.

Also, I got rid of a bunch of crap and some code in the spreadsheet and now it's moving perfectly and there are no delays! Thanks for the formulas and for the advice! Much appreciated James.