Please help solve... basic

[Fegal]

Hello
I have this formula looking at the sheet below it, i would expect a return in D23 of 18..
what am I doing wrong?

TIA!!

 

[Peter_SSs]

Pretty hard to tell anything from an image. Can you give us those two sets of sample data with XL2BB so we can easily copy and use your data to test/investigate?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

what are JAMESJOB JAMESTIME?
If you are trying to find something between a range of dates, what are the date top and bottom?

 

[Fegal]

Thanks for replies.
XL2BB is crashing excel for some reason.. i dont know.
Jamesjob is column K, jamestime is column j.

I was asking Monthly invoicing D23 to sum all hours in Jamestime if jamesjob = c23... and month = b23
ill keep trying with XL2BB...

 

[awoohaw]

What are the values in the Month columns? Is it text "May" or Date(2023,5,#) formatted as "mmm"?
Do you have any spaces in the word "Crawford"?

 

[Fegal]

May is full date format at mmm.
The Crawfords in the ranges are selected from drop down loss referencing b7.

 

[awoohaw]

Do JamesTime and JamesJob start at K7 and J7?

 

[awoohaw]

Two other observations.
1. Your condition at the start looking for crawford does not "filter" the values you're summing. When you add other jobs like Smith or Jones, they will still be summed on line 23. Although that doesn't answer your problem.

2. Why do you separate the names of the person like JamesJOB, JAMESTIME... I'm guessing you're doing ranges for the other names on the grid as well... like MICHAELJOB, MICHAELTIME, 2JOB, 2TIME? You can still filter by putting these names in a column and having an addition IFS criteria. You would not need to change your formula in the middles of the columns.

 

[Fegal]

	Rates					James
Job	Builder	Apprentice			Column1	Start	Stop	Time	Job
Crawford	65	40		May	1/05/2023	07:30	17:00	9.00	Crawford
Crawford Extras	65	40		May	1/05/2023	07:30	17:00	9.00	Crawford
Auckland rd Lot 1	65	40		May	1/05/2023
Auckland Rd Lot 2	65	40		May	1/05/2023
Auckland Rd extras	65	40		May	2/05/2023
88 TeAwa Ave	65	40		May	2/05/2023
				May	2/05/2023
				May	3/05/2023
				May	3/05/2023
				May	3/05/2023
				May	4/05/2023

		JS	MT	1	2
Jan							0
Jan							0
Jan							0
Jan							0
Jan							0
Feb							0
Feb							0
Feb							0
Feb							0
Feb							0
Mar							0
Mar							0
Mar							0
Mar							0
Mar							0
Apr							0
Apr							0
Apr							0
Apr							0
Apr							0
May	Crawford	9.00	9.00	0.00	0.00		1
May							1
May							1
May							1
May							1

 

[awoohaw]

Two other observations.
1. Your condition at the start looking for crawford does not "filter" the values you're summing. When you add other jobs like Smith or Jones, they will still be summed on line 23. Although that doesn't answer your problem.

2. Why do you separate the names of the person like JamesJOB, JAMESTIME... I'm guessing you're doing ranges for the other names on the grid as well... like MICHAELJOB, MICHAELTIME, 2JOB, 2TIME? You can still filter by putting these names in a column and having an addition IFS criteria. You would not need to change your formula in the middles of the columns.

nevermind on my 1st item there. I was not reading the formula correctly. My apologies.