Help with a function/formula

[julievandermeulen]

I need help writing a function/formula

Dropbox

www.dropbox.com

in tab Sheet 3
in cell D7
I want it to subtract .5 if there is a "x" in Cells A2:A5 but I can't figure it out.

 

[Michael M]

Try

=IF(COUNTIF(A2:A5,"x"),SUM(D2:D5)-0.5,SUM(D2:D5))

but can there be more than 1 "x"

 

[bebo021999]

=SUM($D$2:$D$6)-COUNTIF($A$2:$A$5,"x")*0.5

 

[kvsrinivasamurthy]

Try.

=SUM(D2:D6)-0.5*SUMPRODUCT(1*(A2:A5="x"))

 

[julievandermeulen]

Try

=IF(COUNTIF(A2:A5,"x"),SUM(D2:D5)-0.5,SUM(D2:D5))

but can there be more than 1 "x"

Try

=IF(COUNTIF(A2:A5,"x"),SUM(D2:D5)-0.5,SUM(D2:D5))

but can there be more than 1 "x"

Awesome. Is there a way to calculate it by row and the information in column f.
For example. if cells A2:a5 has an "x" the subtract .5 from the row the x is in. and also calculate columns b & c depending on if there is an "s" in column f.
F2 & F3 have a "S" so B2:c3 = 3:00 and because A3 has a "x" it should be 2.50

Dropbox

www.dropbox.com

 

[bebo021999]

Not sure what you need for new "s" in column F, but try:

In D2 then drag down

=SUM(C2-B2)*24-(A2="x")*(F2="s")*0.5

 

[julievandermeulen]

I needed to change this to categorize things better. If I use sumproduct I can get part of what I'm looking for but I need to subtract .5 from the total for the category type of the row that the "x" is in. I can't figure out how to write the formula.

Dropbox

www.dropbox.com

 

[kvsrinivasamurthy]

Calender formation for Excel 365 version.xlsx
	A	B	C	D	E	F	G
1	lunch	time in	time out	total time
2		08:00	09:00	1		s
3	x	09:00	11:00	2		s
4		11:00	11:30	0.5		r
5		11:30	12:00	0.5		b
6			Total
7			r	0.5
8			s	2.5
9			b	0.5
10			grand total	3.5
11
Sheet4

Cell Formulas
Range	Formula
D7:D9	D7	=SUMPRODUCT(($F$2:$F$5=$C7)*((($C$2:$C$5-$B$2:$B$5)*24)-(($A$2:$A$5="x")*0.5)))

In D7 copied down.

=SUMPRODUCT(($F$2:$F$5=$C7)*((($C$2:$C$5-$B$2:$B$5)*24)-(($A$2:$A$5="x")*0.5)))

 

[julievandermeulen]

Calender formation for Excel 365 version.xlsx
	A	B	C	D	E	F	G
1	lunch	time in	time out	total time
2		08:00	09:00	1		s
3	x	09:00	11:00	2		s
4		11:00	11:30	0.5		r
5		11:30	12:00	0.5		b
6			Total
7			r	0.5
8			s	2.5
9			b	0.5
10			grand total	3.5
11
Sheet4

Cell Formulas
Range	Formula
D7:D9	D7	=SUMPRODUCT(($F$2:$F$5=$C7)*((($C$2:$C$5-$B$2:$B$5)*24)-(($A$2:$A$5="x")*0.5)))

In D7 copied down.

=SUMPRODUCT(($F$2:$F$5=$C7)*((($C$2:$C$5-$B$2:$B$5)*24)-(($A$2:$A$5="x")*0.5)))

Awesome! Thanks so much!!

 

[Peter_SSs]

A few comments. Since you have Microsoft 365 you don't need the relatively inefficient SUMPRODUCT as the more efficient SUM will work for you here. Your version also allows to calculate all the results, including the grand total, with a single formula. That is, no need to copy it down.
You can also do without all the $ signs in that case and there is some unnecessary parentheses in the current formula.

25 03 29.xlsm
	A	B	C	D	E	F
1	lunch	time in	time out	total time
2		08:00	09:00	1		s
3	x	09:00	11:00	2		s
4		11:00	11:30	0.5		r
5		11:30	12:00	0.5		b
6			Total
7			r	0.5
8			s	2.5
9			b	0.5
10			grand total	3.5
Sheet3

Cell Formulas
Range	Formula
D7:D10	D7	=LET(v,BYROW(C7:C9,LAMBDA(rw,SUM((F2:F5=rw)*((C2:C5-B2:B5)*24-(A2:A5="x")*0.5)))),VSTACK(v,SUM(v)))
Dynamic array formulas.