How to put countif function into array formula?

dunganbrendan

New Member
Joined
Aug 17, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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.



1629225986044.png
1629226107993.png


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)
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
391
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Is this what you want?
Mr Excel Playground 3.xlsm
ABCDEFGH
1employeemonthly payroll
2Criteria: Countif/sumif total is less than or equal to:713000Sum74050
3145000Count7
4
512450
617200
713600
8
918900
10
1113900
1214200
1317400
1418400
1515500
1617770
Sheet13
Cell Formulas
RangeFormula
H2H2=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D2:D16,,,ROW(INDIRECT("1:"&ROWS(D2:D16))),1))<=B2),E2:E16)
H3H3=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(D2:D16,,,ROW(INDIRECT("1:"&ROWS(D2:D16))),1))<=B2),--(D2:D16=1))
 
Solution

dunganbrendan

New Member
Joined
Aug 17, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
@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.

1629234500096.png


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.

1629234734026.png


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.

1629234914751.png



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

JamesCanale

Active Member
Joined
Jan 13, 2021
Messages
391
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
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

New Member
Joined
Aug 17, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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

Active Member
Joined
Jan 13, 2021
Messages
391
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 17, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 17, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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

Active Member
Joined
Jan 13, 2021
Messages
391
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Aug 17, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,173
Messages
5,745,183
Members
423,931
Latest member
thangvan114

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top