COUNT multiple SUM COUNTIFS

Ally_D

New Member
Joined
Oct 17, 2024
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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!!!!!
 

Attachments

  • Picture1.png
    Picture1.png
    126.8 KB · Views: 5

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Added mini-sheet in case that helps
Capability Development Priorities.xlsm
EFGHIJ
5SectorChoice1Choice2Choice3Choice4Choice5
15Construction and Engineering11
16Construction and Engineering11411
39Construction and Engineering4
40Construction and Engineering4
117Construction and Engineering17
120Construction and Engineering18
Data
 
Upvote 0
Welcome to the MrExcel board!

Does this do what you want?
Excel Formula:
=SUMPRODUCT((Choices<5)*(Choices<>"")*(Data[Sector]="Construction and Engineering"))
 
Upvote 0
Welcome to the MrExcel board!

Does this do what you want?
Excel Formula:
=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).
 
Upvote 0
Is this what you mean?

Excel Formula:
=SUMPRODUCT((Choices>=17)*(Choices<=19)*(Data[Sector]="Construction and Engineering"))
 
Upvote 0
Solution
Is this what you mean?

Excel Formula:
=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 :-)
 
Upvote 0

Forum statistics

Threads
1,224,244
Messages
6,177,391
Members
452,773
Latest member
D P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top