COUNTIFS Value Error

[Kingsof82]

Hi,

I'm looking to summarise in one table, the number of times a specified date value appears within a range in a separate table, when other columns match across the tables. This is the source table:

And this is the destination table containing the troublesome formula:

The COUNTIFS formula I set up was as follows:

=COUNTIFS(Table43[Surname],[@Surname],Table43[Forename],[@Forename],Table43[[P1]:[P12]],Table5[[#Headers],[04/11/2024]])

I've read on a few other threads that this can be bypassed by utilising the SUMPRODUCT function, but as yet, I've not been able to get it to work.

Any help or insight greatly appreciated, thanks.

 

[RoryA]

The headers of a table are always text, and you are trying to use one as a date, which will probably stop your SUMPRODUCT version from working unless you coerce it to a date - for example:

=SUMPRODUCT((Table43[Surname]=[@Surname])*(Table43[Forename]=[@Forename])*(Table43[[P1]:[P12]]=(Table5[[#Headers],[04/11/2024]]+0)))

 

[Kingsof82]

That's worked a treat, thanks.

Is there also a way I can copy this formula into adjacent cells, to still look at the 'Surname', 'Forename' and 'P1:P12' columns and ranges, but change which date column on the destination table I'm matching to? Ordinarily I'd use the '$' symbol but if that's still the case here, then I'm not putting it in the correct place.

Thanks again

 

[RoryA]

I'm not a huge fan of structured references in tables, in large part because of how you handle absolute column references - you basically have to double up the column name:

=SUMPRODUCT((Table43[[Surname]:[Surname]]=[@Surname])*(Table43[[Forename]:[Forename]]=[@Forename])*(Table43[[P1]:[P12]]=(Table5[[#Headers],[04/11/2024]]+0)))

 

[Kingsof82]

That's worked in terms of looking at the same columns in the source table, but the surname and forename columns to match to in the destination table are still changing when I move it across? Is it the same principle of doubling the column name?

 

[RoryA]

Sorry - I missed those. Basically yes, but you have to include the table name too and the @ moves:

=SUMPRODUCT((Table43[[Surname]:[Surname]]=Table43[@[Surname]:[Surname]])*(Table43[[Forename]:[Forename]]=Table43[@[Forename]:[Forename]])*(Table43[[P1]:[P12]]=(Table5[[#Headers],[04/11/2024]]+0)))

 

[Kingsof82]

You're a scholar and a gent, I've now got it working how I had it set out in my head.

Thank you very much

 

[RoryA]

Glad we could help.