Sim if with conditions

N0t Y0urs

Board Regular
Joined
May 1, 2022
Messages
96
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. MacOS
  2. Mobile
  3. Web
Hi
I want a formula that will sum data in a range based on a set criteria and I’m struggling.

I have 1 - 100 data sets that I want to total 3 different columns with. Currently my formula reads = sumif(acc!$d$4:$ec$4,’FWD’,acc!$d5:$ec5) and that’s working to give me the total for all 100 data sets. But now I want only to sum each data set group set 1-20, 21-40 etc

I’ve tried adding an additional IFS function but I keep getting an error so I am hitting my head against a wall.

Here is a link to the sheet Help Please.xlsx

The page I am working on is totals

Thanks
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I figured out the additional If equation but it leads me to my next problem. So my formula now reads =if(ACC!$D$3:$EC$3<20,SUMIF(ACC!C$4:EB$4,"FWD",ACC!$D58:$EC58)) but i want it to add values up to and including my criteria. So if I over complicate my spreadsheet then I could use this formula to get the info I want but I would rather merge the two formulas together. Is that possible

my second formula- =SUMIFS(Totals!$C$13:$C$120,Totals!$D$13:$D$120,"<="&$B$3) where <=“&$b$3 is my criteria that increments in values of 5
 
Upvote 0
@N0t Y0urs - It is perfectly fine to mark your own answer as the solution, however, I don't see that your second post answers the question. On the contrary, it leads to a new question on the same subject that you are seeking help on. If you mark a post without the question being answered then it is unlikely a helper visit your question by thinking that it was already answered, so I removed the solution mark from your post.

Also, that would be great if you only mark a post as the solution if it contains an answer to help future readers.
 
Upvote 0
thanks for reply. I’ve actually given up on streamlining this and have made my spreadsheet quite bulky. My statement above referenced that I had a start of a formula to get where I wanted but didn’t really work then my adjustments still didn’t make it work.
So I threw everything out the window and started again and still no closer to what I want so I’ve now calculated everything by hand and thought I was doing the right thing closing the topic off
 
Upvote 0
I wouldn't give up so quickly. Your sheets could be improved upon, as they are difficult to follow, but I believe you can obtain the subgroup sums as follows:

In your ACC sheet, add an indexing row so that all columns of any given data set (including the blank column that follows the TP, FWD, DB, RB columns) have the same number. This shows a single formula that will spill to the right to fill all columns, assuming you place it over the 1st TP column...note the row (here I've placed it in row 24...note that the formula is set up to spill for the same number of columns between D:EC, inclusive, so by placing it in D24, the last spilling value will be in EC24):
MrExcel_20220514_Help Please.xlsx
ABCDEFGHIJKLMN
24Data Sets-->11111222223
25Day#Data Set1$ 25.00Data Set2$ 10.00
26TPFWDDBRBTPFWDDBRBTP
27Mon1$ 0.70$ 25.70$ 1.00
282$ 0.70$ 26.39
293$ 1.28$ 27.67
ACC
Cell Formulas
RangeFormula
D24:EC24D24=QUOTIENT(SEQUENCE(1,COLUMNS(D:EC),0),5)+1
J25J25=E25
K25K25=F25+1
L25L25=E37
D27:D29D27=Inputs!$J10
G27G27=G25+D27-E27-F27
G28:G29G28=G27+D28-E28-F28
Dynamic array formulas.

Then when you compute totals, it appears that you want the 1st entry for every day, so column C values of {1;6;11;16;...} are of interest.
MrExcel_20220514_Help Please.xlsx
ABCD
24Data Sets-->1
25Day#
26TP
27Mon1$ 0.70
282$ 0.70
293$ 1.28
304$ 1.87
315$ 2.45
32Tue6$ 2.85
337$ 4.02
348$ 5.30
359$ 6.77
3610$ 8.05
37Wed11$ 10.79
3812$ 13.53
3913$ 17.74
4014$ 21.94
4115$ 24.69
42Thu16$ 27.43
ACC
Cell Formulas
RangeFormula
D24:EC24D24=QUOTIENT(SEQUENCE(1,COLUMNS(D:EC),0),5)+1
B32,B42,B37B32=B27+1
D27:D42D27=Inputs!$J10
Dynamic array formulas.

We note that if we divided each of these values by 5 and consider only the remainder, all have a remainder of 1. We use this idea to construct part of the formula for your Overview sheet.
Excel Formula:
MOD(ACC!$C$27:$C$526,5)=1
This will identify only the rows indexes of interest on the ACC sheet.
Now let's say we wanted to create subgroups of the data sets by grouping every 6 together...so I enter a 6 in J5 (you actually want 20 there) and the subgroup data sets appear to the right (these are here for illustration purposes only):
MrExcel_20220514_Help Please.xlsx
IJKLMNOPQ
3Start at dataset1
4Number of datasets26
5Number datasets in ea subgroup6123456
6Number col ea subgroup4789101112
7Number subgroups5131415161718
8192021222324
9252627282930
10313233343536
11373839404142
Overview
Cell Formulas
RangeFormula
J4J4=MAX(ACC!$D$24:$EC$24)
J7J7=CEILING((J4-J3+1)/J5,1)
L5:Q11L5=SEQUENCE(1,$J$5,$J$3+$J$5*(ROWS($5:5)-1))
Dynamic array formulas.

These arrays of data set numbers are created using the SEQUENCE formula shown and then pulled down row by row to create subsequent subgroup dataset numbers. I haven't placed any error checking in this formula, so it is possible to pull the formula down to create data set numbers that do not exist. In any case, this idea is also used in the main Overview formula to identify columns of interest. Specifically, it looks like this:
Excel Formula:
ISNUMBER(MATCH(ACC!$D$24:$EC$24,SEQUENCE(1,$J$5,$J$3+$J$5*(ROWS($5:5)-1)),0)
where we match the data set numbers (which were added in the first step above) to those that need to be included in each subgroup, and where there is a match, a number will be found in the resulting array, and an error code will be found where no matches are found. We then run a logic check on this array to evaluate where we have numbers, and those are the column index positions of interest. There is another more obvious column-matching condition that requires that the text in the Overview table heading (FWD, DB, RB) must also match the column heading in the ACC table.
Excel Formula:
ACC!$D$26:$EC$26=C$4
Putting these ideas together, the Overview table would look like this:
MrExcel_20220514_Help Please.xlsx
BCDEIJ
3Start at dataset1
4FWDDBRBNumber of datasets26
51 - 20$ 2,810.00$ 69,345.67$ 1,318,896,185.30Number datasets in ea subgroup20
621 - 40#VALUE!#VALUE!#VALUE!Number col ea subgroup4
741 - 60#VALUE!#VALUE!#VALUE!Number subgroups2
861 - 80#VALUE!#VALUE!#VALUE!
981 - 100#VALUE!#VALUE!#VALUE!
10
11Total #VALUE!#VALUE!#VALUE!
Overview
Cell Formulas
RangeFormula
J4J4=MAX(ACC!$D$24:$EC$24)
C5:E9C5=SUM(FILTER(FILTER(ACC!$D$27:$EC$526,(ACC!$D$26:$EC$26=C$4)*ISNUMBER(MATCH(ACC!$D$24:$EC$24,SEQUENCE(1,$J$5,$J$3+$J$5*(ROWS($5:5)-1)),0)),""),MOD(ACC!$C$27:$C$526,5)=1,""))
J7J7=CEILING((J4-J3+1)/J5,1)
C11:E11C11=SUM(C5:C9)

If data are present on the ACC sheet, you shouldn't get any errors, and I haven't placed any error suppression features in the formula just yet. Let me know if this produces expected results. The SEQUENCE function that appears in this main formula could be streamlined somewhat if you do not want the flexibility to make different size subgroups or start the summary with a different data set number (other than 1).
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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
Back
Top