Counting data with multiple criteria from different columns

Dear All,

This is my first post. I have been having trouble with Excel and I am hoping to get some help.

I am trying to count data using multiple criteria in different columns. I.e. counting values in one column that are between 500 and 1000 and in another column - either “fixed deposits” or call deposits.

The results of the formula below are the count for the values within the range (500 to 1000) that are “Fixed deposits” but the call deposits are not being counted.

I know there is a simple of way doing this, but I can’t figure it out. Please help.
=+COUNTIFS(I2:I11,"<="&1000,' Clients'!I2:I11,">="&500,$F$2:$F$11,{"FIXED DEPOSITS","CALL DEPOSITS"})

Your assistance is much appreciated

Kindest Regards
Muammar

Sorry the formula I am actually using

=COUNTIFS($B$2:$B$11,"<="&Parameters!$B$1,Data!$B$2:$B$11,">="&Parameters!$B$2,A2:A11,{"FIXED DEPOSITS","CALL DEPOSITS"})

and the correct file is attached to this message.

Thanks JP. Simple solution but its going to take me a long way. Appreciate it.

Is it possible to extend this formula to a third column, (i.e. third criteria)?

Thanks again. So I assume one more column with more than one criterion will not be possible?

You’re right, that worked. I know I am pushing it but is it possible to add more than one criterion from a fourth column?

Cheers. About to leave the office. Will definitely try that tomorrow and get back to you.

Hi Joseph,

I used the formula suggested for counting the data in numerous columns with numerous criterion and added one column to the same formula as below.

=SUMPRODUCT((ABS($B$2:$B$38)<=Parameters!$B$1)*(ABS($B$2:$B$38)>=Parameters!$B$2)*(ISNUMBER(MATCH($A$2:$A$38,{"FIXED DEPOSITS","CALL DEPOSITS"},0)*(ISNUMBER(MATCH($C$2:$C$38,{"CORPORATE","PUBLIC SECTOR"},0))))))

I am getting a result of 6 instead of 5. I assume it’s the wrong use of the formula. Please advice?

I have attached my workbook to assist.

Kindest Regards
Muammar

Thanks again.

On another topic…. Assuming I have formulas that pick a set of data from the first to the last row and I want to add a new set of data, is it possible to make sure all the formulas I am applying to the original set pick the new data, i.e. as opposed to changing them manually?

Okay. I shall do that.