SUMPRODUCT/COUNTIF but only count once

[NikToo]

This one is a bit hard to explain, but hoping it makes sense...

I have a table with a list of people in one column, a "source" column, and then daily sales figures with date as the header, and the week number above it. What I want to do is count how many people had sales in a given week.

	Week	43	44	44	44	44	44	44	44	45	45
Name	Source	30/10/2021	31/10/2021	01/11/2021	02/11/2021	03/11/2021	04/11/2021	05/11/2021	06/11/2021	07/11/2021	08/11/2021
Adam Ant	Campaign 1	$10	0	0	0	0	0	0	0	$10	0
Betty Boop	Campaign 2	0	0	0	0	$150	0	0	0	0	0
Charlie Chan	Campaign 3	0	$50	0	$50	0	0	$50	0	$50	0

So like this essentially. What I want to be able to say is that 2 people had sales in week 44 (imagine columns either side of this down to week 1), regardless of how much (easy to sumproduct that). I tried a SUMPRODUCT(COUNTIF) but that would say that I have 4 cells which are greater than zero. Is there a way of checking the entire range?

 

[Eric W]

How about:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1		Week	43	44	44	44	44	44	44	44	45	45
2	Name	Source	30/10/2021	31/10/2021	1/11/2021	2/11/2021	3/11/2021	4/11/2021	5/11/2021	6/11/2021	7/11/2021	8/11/2021
3	Adam Ant	Campaign 1	$10	0	0	0	0	0	0	0	$10	0
4	Betty Boop	Campaign 2	0	0	0	0	$150	0	0	0	0	0
5	Charlie Chan	Campaign 3	0	$50	0	$50	0	0	$50	0	$50	0
6
7
8
9	Week	# of sales	# of sellers
10	44	4	2
Sheet17

Cell Formulas
Range	Formula
B10	B10	=SUMPRODUCT((C3:L5>0)*(C1:L1=A10))
C10	C10	=SUM(--(MMULT((C3:L5>0)*(C1:L1=A10),SEQUENCE(COLUMNS(C1:L1)))>0))

 

[NikToo]

Thanks, that works! I had to add another dimension and do some dynamic ranges. I'm sure it's summoned a demon or something as well (Access does ask if I want a wizard to analyse my table, so I'm guessing it's a thing).

Thanks again.

 

[Eric W]

Cool! I'm glad you got it working. Thanks for the update! ?

? <--- There's your demon!