COUNT multiple SUM COUNTIFS

[Ally_D]

Hi All, first time poster here. I would be grateful for your help please.

I am trying to find out the COUNT of instances in two columns of data (screenshot below). I have written formulas that COUNT the instances in a single column of data, but I have not been able to combine the formulas together.

In the example below, I have COUNTed the instances of “Construction” in table column ‘Sector’: =SUM(COUNTIFS((Data[Sector]),"Construction and Engineering")) This gives the correct result of 6. Note, the name of this sheet is ‘Data’ but my formula is being entered on a different sheet hence the use of ((Data[Sector]).

I have also COUNTed the instances of variables 1,2,3 or 4 in columns F:J (which is a named range called ‘Choices’) like so: =SUM(COUNTIFS(Choices,{1,2,3,4})) This gives the correct result of 51 (including counting the rows not shown due to the filter on column E).

So now, I would like to find the COUNT of ‘Choices’ 1,2,3 or 4 from the sectors ‘Construction’. The result should be 9.

Should I be using the SUMPRODUCT function? I have tried:
=SUMPRODUCT(COUNTIFS((Data[Sector]),“Construction”,Choices,{1,2,3,4})) but I get the error #VALUE! – same result whether I use ENTER or CTRL+SHIFT+ENTER.

This is driving me crazy!!!!!

 

[Ally_D]

Added mini-sheet in case that helps

Capability Development Priorities.xlsm
	E	F	G	H	I	J
5	Sector	Choice1	Choice2	Choice3	Choice4	Choice5
15	Construction and Engineering	1	1
16	Construction and Engineering	1	1	4	1	1
39	Construction and Engineering	4
40	Construction and Engineering	4
117	Construction and Engineering	17
120	Construction and Engineering	18
Data

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

=SUMPRODUCT((Choices<5)*(Choices<>"")*(Data[Sector]="Construction and Engineering"))

 

[Ally_D]

Welcome to the MrExcel board!

Does this do what you want?

=SUMPRODUCT((Choices<5)*(Choices<>"")*(Data[Sector]="Construction and Engineering"))

Thank you!!!!!!!!!!!

If I wanted to now COUNT the Choices between two numbers how would I do that? For example, rather than counting the number of Choices lower than number 5, as in the first argument, I would like to count the number of choices between 17 and 19 inclusive - the mini-sheet shows that this situation in rows 117 and 120 (just so happened that no-one chose choice 19 yet but they might do in the future).

 

[Peter_SSs]

Is this what you mean?

=SUMPRODUCT((Choices>=17)*(Choices<=19)*(Data[Sector]="Construction and Engineering"))

 

[Ally_D]

Is this what you mean?

=SUMPRODUCT((Choices>=17)*(Choices<=19)*(Data[Sector]="Construction and Engineering"))

Excellent - I was almost there myself but forgot to remove the <>""in the first solution you offered.

Have a wonderful day - you have certainly made mine a bit better now

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.