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
• 27,940 points
83 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
• 51,600 points

Related Questions In Others

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
• 51,600 points
66 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 in Others by narikkadan
• 51,600 points
87 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 in Others by narikkadan
• 51,600 points
53 views
0 votes
1 answer

How to extract text before the last "," in excel formula?

Using FILTERXML() • Formula used in cell B1 =FILTERXML("<m><b>"&SUBSTITUTE(A1,", ","</b><b>")&"</b></m>","//b[last()-1]") You can ...READ MORE

answered Jan 20 in Others by narikkadan
• 51,600 points
30 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
• 51,600 points
103 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
• 51,600 points
201 views
0 votes
1 answer

Want to compare two columns in excel

Hello To compare two columns in excel ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
361 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
206 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
• 51,600 points
141 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
• 51,600 points
133 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