How to use COUNTIFS with multiple conditions and columns in Excel

0 votes

In Excel, I'm attempting to use COUNTIFS to count the number of values based on several conditions and numerous columns. This is an illustration of what I'm attempting:

ColumnA        ColumnB
16             12.4
-              2
15             -
8              -
-              25
-              12
20             23
15             19
12.5           10 

When ColumnA exceeds 12.5 and when each value in ColumnB exceeds the equivalent value in the same row when compared to ColumnA, those are the conditions I'm looking to look for. Because only two rows in ColumnA have a value more than 12.5 and where the matching value in ColumnB is greater than the value in ColumnA, the count in the sample data shown above should be 2.

I've tried this but it's giving me a 0 count,

=COUNTIFS(A1:A9, ">12.5", B1:B9, ">A1:A9")
Apr 4, 2023 in Others by narikkadan
• 63,700 points
363 views

1 answer to this question.

0 votes

Use  SUMPRODUCT and double unay operators:

enter image description here

=SUMPRODUCT(--(A1:A9>12.5)*--(B1:B9>A1:A9)*--(ISNUMBER(B1:B9)))
answered Apr 4, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

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

How to Freeze Top Row and Apply Filter in Excel Automation with C#

Try this: // Fix first row workSheet.Activate(); workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,700 points
2,226 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,700 points
7,038 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
582 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,476 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,700 points
600 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
715 views
0 votes
1 answer

How to simplify adding multiple countifs formula in excel

If the Excel version you are using ...READ MORE

answered Nov 26, 2022 in Others by narikkadan
• 63,700 points
440 views
0 votes
1 answer
0 votes
1 answer

How to compare 2 cells with delimited items in each and output the difference in items?

The following function would do this for ...READ MORE

answered Feb 23, 2023 in Others by Kithuzzz
• 38,010 points
367 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