Formula =SUMIF for a sum range of the first 4 digits only in the cells

[schrieber]

I'm using =SUMIFS($D$18:$D$33,$B$18:$B$33,B4) which works well, but I need the formula to read only the first 4 digits in the sum range of $D$18:$D$33 then have those first 4 digits that match what is in B4 so that it can return the sum of the criteria range $B$18:$B$33. Can this be done? (The data in the cells will be changing each month, so each month I will edit the 4 digits in B4 and need to have the first 4 digits of the sum range match what is in B4.) Can this be done? Thanks in advance!

 

[awoohaw]

Hi, welcome to the forum. Your question has some complexity that a prose description doesn't overcome for me.
Mr. Excel has a tool, xl2bb (link below) where you can post a mini sheet of your data that allows the forum to see your calculations and values very effectively.
If you cannot install it, the please post your data as a table (and you would have to label columns and rows, please).

Thanks in advance.

 

[schrieber]

I couldn't install xl2bb.. Here's the table:

1	A​	B​	C​	D​
2	​	​	​
3	​	Project #​	Project #​
4	​	4311​	3637​
5	​	Total Hours:​	Total Hours:​
6					Example: Need formula in B6 to return the total hours of the project # in B4 which resides in range B$18:$B$23, with the sum range of $D$18:$D$23 (So the total in B6 would be 6.5)
17		Project Name	Type	Project Hours
18		4311-GENERIC.00	HB	6.5
19		9999-ADMIN.00	NB	1.0
20		3637-ALPHA	PB	1.5
21		3637-MAIN	PB	21.0
22
23

 

[kweaver]

What about:

=SUM((C4=1*LEFT($C$18:$C$21,4))*$E$18:$E$21)

 

[awoohaw]

Thanks for the table data. Try this:

Book1
	A	B	C	D	E
1
2
3		Project #	Project #
4		4311	3637
5		Total Hours:	Total Hours:
6		6.5	22.5		Example: Need formula in B6 to return the total hours of the project # in B4 which resides in range B$18:$B$23, with the sum range of $D$18:$D$23 (So the total in B6 would be 6.5)
7
8		Project Name	Type	Project Hours
9		4311-GENERIC.00	HB	6.5
10		9999-ADMIN.00	NB	1
11		3637-ALPHA	PB	1.5
12		3637-MAIN	PB	21
Sheet3

Cell Formulas
Range	Formula
B6:C6	B6	=SUM((--(ISNUMBER(FIND(B$4,$B$9:$B$12))))*($D$9:$D$12))

 

[schrieber]

I tweaked your reply to be =SUM((B4=1*LEFT($B$18:$B$21,4))*$D$18:$D$21) and received the answer I need, but if I change the formula to include the blank cells through 23, I get an error . For instance, =SUM((B4=1*LEFT($B$18:$B$23,4))*$D$18:$D$23) returns a #VALUE!. I will need to include the ranges of of those blank lines as they may have info in them in future months.

Thank you!

 

[awoohaw]

I tweaked your reply to be =SUM((B4=1*LEFT($B$18:$B$21,4))*$D$18:$D$21) and received the answer I need, but if I change the formula to include the blank cells through 23, I get an error . For instance, =SUM((B4=1*LEFT($B$18:$B$23,4))*$D$18:$D$23) returns a #VALUE!. I will need to include the ranges of of those blank lines as they may have info in them in future months.

Thank you!

You should not get an error with the formula in Post #5.

Book1
	A	B	C	D	E
1
2
3		Project #	Project #
4		4311	3637
5		Total Hours:	Total Hours:
6		6.5	22.5		Example: Need formula in B6 to return the total hours of the project # in B4 which resides in range B$18:$B$23, with the sum range of $D$18:$D$23 (So the total in B6 would be 6.5)
7
8		Project Name	Type	Project Hours
9		4311-GENERIC.00	HB	6.5
10		9999-ADMIN.00	NB	1
11		3637-ALPHA	PB	1.5
12		3637-MAIN	PB	21
Sheet3

Cell Formulas
Range	Formula
B6:C6	B6	=SUM((--(ISNUMBER(FIND(B$4,$B$9:$B$19))))*($D$9:$D$19))

 

[schrieber]

Thanks for the table data. Try this:

Book1
	A	B	C	D	E
1
2
3		Project #	Project #
4		4311	3637
5		Total Hours:	Total Hours:
6		6.5	22.5		Example: Need formula in B6 to return the total hours of the project # in B4 which resides in range B$18:$B$23, with the sum range of $D$18:$D$23 (So the total in B6 would be 6.5)
7
8		Project Name	Type	Project Hours
9		4311-GENERIC.00	HB	6.5
10		9999-ADMIN.00	NB	1
11		3637-ALPHA	PB	1.5
12		3637-MAIN	PB	21
Sheet3

Cell Formulas
Range	Formula
B6:C6	B6	=SUM((--(ISNUMBER(FIND(B$4,$B$9:$B$12))))*($D$9:$D$12))

I used your format and changed it to: =SUM((--(ISNUMBER(FIND(B$4,$B$18:$B$26))))*($D$18:$D$26)) and this worked! Thank you!

 

[Tetra201]

An alternative solution: add a (hidden) helper column with the four-digit prefixes of your projects (e.g., =LEFT(B18,4)) then use a regular SUMIF/SUMIFS.

 

[schrieber]

What about:

=SUM((C4=1*LEFT($C$18:$C$21,4))*$E$18:$E$21)

I tweaked your reply to be =SUM((B4=1*LEFT($B$18:$B$21,4))*$D$18:$D$21) and received the answer I need, but if I change the formula to include the blank cells through 23, I get an error . For instance, =SUM((B4=1*LEFT($B$18:$B$23,4))*$D$18:$D$23) returns a #VALUE!.

Thank you!