CODEWITHSUNDEEP
arraysexcelformulaexcel-2013
RolloTreadway
RolloTreadway

Reputation: 33

COUNTIFS with multiple criteria from cell references (trying to use an array)

First time I've asked a question, so please bear with me.

I have a formula (in Excel 2013): 

=SUM(COUNTIFS(Data[Location],{76,876},Data[Difference],"<=2",Data[Year],2017))

It's counting how many tasks at a particular location have been done within 2 weeks, by two different teams at that location, identified as 76 and 876, with all the data stored in a table imaginatively named Data. And it works fine.

However, I'd like to be able to do this with many different teams, all identified by a number in another range. If I just wanted one team from each location, I could 

=SUM(COUNTIFS(Data[Location],A2,Data[Difference],"<=2",Data[Year],2017))

but I can't (as far as I can tell?) ask it to look for multiple locations with cell references, like {A2,B2}.

Is there any way around this? Or should I just add a helper column in my Data table which would link 76 and 876 (and every other pair of teams at the same location)? I'm wary of doing that, because there's a lot of entries in that table, and a lot of helper columns; every new formula adds extra calculating time, and it's already weighty enough that I can comfortably make a cup of tea in the time it takes to calculate the book.

Many thanks.

minor edit: I realise that in the second line of code, I wouldn't need the SUM function, copypaste error, sorry

Upvotes: 0

Views: 1991

Answers (1)

Axel Richter
Axel Richter

Reputation: 61862

Array literals like {76,876} are only possible using literal values not using references, that's right. But if A2 contains 76 and B2 contains 876, then A2:B2 will be the same as {76,876} if this reference is in array context.

So in your case and if A2 contains 76 and B2 contains 876, you could using:

{=SUM(COUNTIFS(Data[Location],A2:B2,Data[Difference],"<=2",Data[Year],2017))}

This is an array formula. Input it into the cell without the curly brackets and then press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets then appear automatically.

Or you could using SUMPRODUCT since SUMPRODUCT will have all of it's parameters in array context without explicitly pressing [Ctrl]+[Shift]+[Enter] to confirm.

=SUMPRODUCT(COUNTIFS(Data[Location],A2:B2,Data[Difference],"<=2",Data[Year],2017))

Upvotes: 1

Related Questions