Computing Monthly Totals with Variations

[dwgnome]

I have the following table with data

Code	Category	Vx	1/1/24	2/1/24	3/1/24	4/1/24	5/1/24
AT370-3Mx	Primer	2.17	58.5	105	142.5	125	147
AM-29711Mx	Topcoat	0.88	28	24	10.8	20	8
E61W00753-EXP	Primer	2.20	275	110	220	220	220
F75EXA16707	Topcoat	1.88	275	220	220	275	275
E61XXA16720-4366	Primer	3.46	440	110	165	220	330
ENGC11131	Cleaning Solvent CS-2	3.38	100	250	700	100	200
CGC111	Cleaning Solvent CS-1	4.46	300	0	100	5	10
ALK-314003	Topcoat	5.47	0	0	0	1	0
ALK-000761	Topcoat	5.38	0	0	0	0	5
CGC112	Cleaning Solvent CS-1	0.20	400	0	0	0	0
ENGC0	Cleaning Solvent CS-2	0.19	200	0	300	0	0

Current formula for 1/1/24:
'=SUMPRODUCT($C$7:$C$17,(INDEX($D$7:$H$17,,MATCH(A23,$D$6:$H$6,0))))
1/1/24	4,589
2/1/24	2,130

While the formula works as designed, I need a way to modify the formula to account for reduction for cleaning solvents:

For any rows having Cleaning Solvent CS-1 (Col B), then multiply the result of those row calculations by 0.1
So for January instead of 4.46*300, it should be 4.46*300*.1

For any rows having Cleaning Solvent CS-2, then multiply the result of those row calculations by 0.5
So for January instead of 3.38*100, it should be 3.38*100*.5

The correct results for 1/1/24 should then be: 3,126
The correct results for 2/1/24 should then be: 1,707

Please note I still use Excel 2019

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G	H
1
2
3
4
5
6	Code	Category	Vx	1/1/2024	2/1/2024	3/1/2024	4/1/2024	5/1/2024
7	AT370-3Mx	Primer	2.17	58.5	105	142.5	125	147
8	AM-29711Mx	Topcoat	0.88	28	24	10.8	20	8
9	E61W00753-EXP	Primer	2.2	275	110	220	220	220
10	F75EXA16707	Topcoat	1.88	275	220	220	275	275
11	E61XXA16720-4366	Primer	3.46	440	110	165	220	330
12	ENGC11131	Cleaning Solvent CS-2	3.38	100	250	700	100	200
13	CGC111	Cleaning Solvent CS-1	4.46	300	0	100	5	10
14	ALK-314003	Topcoat	5.47	0	0	0	1	0
15	ALK-000761	Topcoat	5.38	0	0	0	0	5
16	CGC112	Cleaning Solvent CS-1	0.2	400	0	0	0	0
17	ENGC0	Cleaning Solvent CS-2	0.19	200	0	300	0	0
18
19
20
21
22		Original	Version1
23	1/1/2024	4589.985	3125.785
24	2/1/2024	2130.17	1707.67
Sheet4

Cell Formulas
Range	Formula
B23:B24	B23	=SUMPRODUCT($C$7:$C$17,(INDEX($D$7:$H$17,,MATCH(A23,$D$6:$H$6,0))))
C23:C24	C23	=SUMPRODUCT($C$7:$C$17*$D$7:$H$17*($D$6:$H$6=A23)*((1+9*ISERROR(SEARCH("Cleaning Solvent CS-1",$B$7:$B$17)))/10)*((1+ISERROR(SEARCH("Cleaning Solvent CS-2",$B$7:$B$17)))/2))

This version should work for you in Excel 2019. It's a bit obtuse though. I tried to find a version that's a bit easier to understand, but I'm running into testing difficulties between our versions. I'll keep plugging at it.

 

[dwgnome]

Hi Eric,

Thank you for that solution as it works.
could you explain how the formula works:

1+9*ISERROR(SEARCH("Cleaning Solvent CS-1",$B$7:$B$17)))/10)

I think it isolates each instance of "Cleaning Solvent CS-1 and assigns 1/10 or 0.1 to each occurrence. However, when I step through the evaluate formula I cannot understand the purpose of the ISERROR, the #VALUE it creates then converts to 9s etc. Could you explain how that works as I may need to adjust other types of products in the future, such as needing to adjust to other fractions.

 

[Eric W]

Sure. SUMPRODUCT has array processing on by default, unless you have an IF or IFERROR function. So we have to find a way to do a conditional without IF.

(1+9*ISERROR(SEARCH("Cleaning Solvent CS-1",$B$7:$B$17)))/10)

The SEARCH function looks for "Cleaning Solvent CS-1" in every cell in B7:B17, and returns either the offset into the cell where the text begins, or an error if it is not found. The ISERROR checks for the error. If there is an error, meaning not found, then it returns TRUE. Multiply that by 9, and it turns into a 9. Add 1, and you get 10, divide by 10 and you get 1, so the given row is multiplied by 1. If there is not an error, meaning that the text is found, then the ISERROR returns FALSE, multiply that by 9 and you get 0, add 1 and divide by 10 and you get .1, which is multiplied by that row. Kind of convoluted, but it works. The other section for CS-2 works the same. You could just use the "CS-1" or "CS-2" if you want to shorten it a bit, as long as those text snippets don't appear anywhere else.

It is possible to enter the formula with Control+Shift+Enter to enable array processing even with an IF included. So this should theoretically work:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4
5
6	Code	Category	Vx	1/1/2024	2/1/2024	3/1/2024	4/1/2024	5/1/2024		Category	Factor
7	AT370-3Mx	Primer	2.17	58.5	105	142.5	125	147		Cleaning Solvent CS-1	0.1
8	AM-29711Mx	Topcoat	0.88	28	24	10.8	20	8		Cleaning Solvent CS-2	0.5
9	E61W00753-EXP	Primer	2.2	275	110	220	220	220
10	F75EXA16707	Topcoat	1.88	275	220	220	275	275
11	E61XXA16720-4366	Primer	3.46	440	110	165	220	330
12	ENGC11131	Cleaning Solvent CS-2	3.38	100	250	700	100	200
13	CGC111	Cleaning Solvent CS-1	4.46	300	0	100	5	10
14	ALK-314003	Topcoat	5.47	0	0	0	1	0
15	ALK-000761	Topcoat	5.38	0	0	0	0	5
16	CGC112	Cleaning Solvent CS-1	0.2	400	0	0	0	0
17	ENGC0	Cleaning Solvent CS-2	0.19	200	0	300	0	0
18
19
20
21
22		Original	Version1	Version2
23	1/1/2024	4589.985	3125.785	4589.985
24	2/1/2024	2130.17	1707.67	2130.17
Sheet4

Cell Formulas
Range	Formula
B23:B24	B23	=SUMPRODUCT($C$7:$C$17,(INDEX($D$7:$H$17,,MATCH(A23,$D$6:$H$6,0))))
C23:C24	C23	=SUMPRODUCT($C$7:$C$17*$D$7:$H$17*($D$6:$H$6=A23)*((1+9*ISERROR(SEARCH("Cleaning Solvent CS-1",$B$7:$B$17)))/10)*((1+ISERROR(SEARCH("Cleaning Solvent CS-2",$B$7:$B$17)))/2))
D23:D24	D23	=SUM($C$7:$C$17*IFERROR(VLOOKUP($C$7:$C$17,$J$7:$K$20,2,0),1)*INDEX($D$7:$H$17,0,MATCH(A23,$D$6:$H$6,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

But the issue here is that Control+Shift+Enter works differently on versions of Excel after 2021, so my testing does not give the expected answer. You can try the version2 formula, which I think should work for you, but I can't validate that. I think you'll agree it's much simpler to understand and update.

 

[dwgnome]

Hi Eric,

Thank you so much for your expertise and explanation of how the ISERROR(SEARCH works. I understand completely.
Also, something I think is pretty cool is that the SEARCH function seems to work for partial word matching without having to use wildcards.
I'll have to experiment with your other idea you presented.

 

[Eric W]

I just noticed that the version2 formula should be:

=SUM($C$7:$C$17*IFERROR(VLOOKUP($B$7:$B$17,$J$7:$K$20,2,0),1)*INDEX($D$7:$H$17,0,MATCH(A24,$D$6:$H$6,0)))

You would still need to use Control+Shift+Enter.

 

[dwgnome]

Thank you for the correction.