How to simplify adding multiple countifs formula in excel

0 votes

I want to count the number of cells that meet two conditions:

  1. sheet ABC's A2:A100 should be equal to the value of sheet XYC cell A8
  2. the cell value in range D2:M100 = 1

Originally, I tried to use this formula:

=COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$D$2:$M$100,1)

But this gave me the error #VALUE

I then decided to use the following formula to count each column separately and add them together.

=COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$D$2:$D$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$E$2:$E$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$F$2:$F$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$G$2:$G$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$H$2:$H$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$I$2:$I$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$J$2:$J$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$K$2:$K$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$L$2:$L$100,1)+
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$M$2:$M$100,1)

I am wondering if there are any other ways that allow me to shorten my formula.

Nov 26, 2022 in Others by Kithuzzz
• 38,010 points
466 views

1 answer to this question.

0 votes

If the Excel version you are using supports dynamic arrays (ms365), you can utilize a boolean structure inside SUMPRODUCT() or plain SUM():

=SUMPRODUCT((ABC!A2:A100=XYC!A8)*(ABC!D2:M100=1))

I hope this helps you.

answered Nov 26, 2022 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
391 views
0 votes
1 answer

Excel How to Remove Duplicate Rows in multiple of 3 Same Value

Put the following formula into a helper ...READ MORE

answered Dec 28, 2022 in Others by narikkadan
• 63,700 points
384 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,700 points
2,049 views
0 votes
1 answer

How to set formula in Excel with POI?

Microsoft Excel only stores en US formula ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,700 points
681 views
0 votes
1 answer

Excel COUNTIF "Column D" year equals 2015 and Column L equals "15 or greater"

COUNTIFS() with an S allows the ability of multiple ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
740 views
0 votes
1 answer

Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF($A$1:$A$8,"A"),COUNTIF($A$1:$A$8,"HA")/2) - total count ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,700 points
1,533 views
0 votes
1 answer

Newbie needs Excel suggestions with a simple script

Formula to Count the Number of Occurrences ...READ MORE

answered Feb 7, 2023 in Others by narikkadan
• 63,700 points
377 views
0 votes
1 answer

Excel: Count distinct numerical values if string condition matches

FILTER Shipper and Month based on Employee ...READ MORE

answered Feb 10, 2023 in Others by narikkadan
• 63,700 points
335 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,700 points
606 views
0 votes
1 answer

How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,700 points
1,100 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP