SUMPRODUCT returning 0

[jimbuck999]

I've had a good look around at trying to figure this out but I am still struggling. I've tried using -- in a few areas but still haven't got it to work, so was going to try here to find the answer.

=SUMPRODUCT((Table1[[C1]:[C3 attended]]>=$I$1)*(Table1[[C1]:[C3 attended]]<$I$1+1)*(Table1[[#Headers],[C1]:[C3 attended]]=$J$1)*(Table1[[C1]:[C3 attended]]=$K$1))

I think it is something to do with the last section being text, but I'm really not sure and still quite the begginer to excel.

Removing the (Table1[[C1]:[C3 attended]]=$K$1) and it works perfectly.

Any help will be appricated.

Thanks

 

[Domenic]

Hi, and welcome to MrExcel.

First, before I address your question, just a couple of things.

1) When posting data, please avoid pictures. Instead, either simply copy and paste your data or use the xl2BB add-in to do so. Then the volunteers here can easily copy and paste the data into their worksheet for coming up with a solution.

2) I would suggest that you update your profile to include your Excel version so that the most efficient solution can be offered.

Now to your question. First, your formula has >=$I$1 and <$I$1+1, which is basically =$I$1. So I think we can replace the two criteria with a single criteria. So it looks like you probably want the following...

jimbuck.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	C1	C1 attended	C2	C2 attended	C3	C3 attended		8/10/2023	C2	Attended	1
2	1	8/10/2023	Attended	8/12/2023							No Show	0
3	2	8/11/2023	Cancelled	8/13/2023	Cancelled	8/10/2023	Cancelled				Cancelled	0
4	3	8/10/2023	No Show	8/10/2023	Attended
5
6
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
L1:L3	L1	=SUMPRODUCT(--(INDEX(Table1[[C1]:[C3 attended]],0,MATCH($J$1,Table1[[#Headers],[C1]:[C3 attended]],0))=$I$1),--(INDEX(Table1[[C1]:[C3 attended]],0,MATCH($J$1,Table1[[#Headers],[C1]:[C3 attended]],0)+1)=K1))

Hope this helps!

 

[jimbuck999]

Hi, and welcome to MrExcel.

Hi Domenic and thanks for the reply. Apologies for the picture, I couldn't use the add on as I was on the a work laptop. Also sorry I should have been clearer in my data, the dates are actually times (if that makes sense, so dd/mm/yy hh:mm, so that's why I used (>=$I$1 and <$I$1+1). If there is a better way lookup a time that is between two given dates (dd/mm/yy) without using the (>=$I$1 and <$I$1+1) then I am all ears

And finally, you're a genius! This works perfectly, also when I adapted it to (>=$I$1 and <$I$1+1)

I always struggle with Match and Index for some weird reason, so I thought it was something like this.

You are amazing thank you very much, you've made me very happy!

 

[Domenic]

Hi Domenic and thanks for the reply. Apologies for the picture, I couldn't use the add on as I was on the a work laptop.

No problem, next time simply copy/paste.

This works perfectly, also when I adapted it to (>=$I$1 and <$I$1+1)

That's great, I'm glad I could help. And I'm glad you were able to amend the formula successfully. Thanks for the feedback.

Cheers!