COUNTIFS Returning 0

MadHatsJess

New Member
Joined
Jul 12, 2017
Messages
27
Hi

I have two sheets.

Sheet one has a Store name, Product Name, Item version and Price. Sheet two has the raw data.

I am using COUNTIFS to find the volume of sales using certain criteria within a cell.

I am using the formula below and it is returing a 0 but it should return 1 in this case

=COUNTIFS('Sheet 2'!A:O,B17,'Sheet 2'!A:O,C17)

Can anyone help please?

Thanks

Jess
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
Both ranges are the same, yet you are trying to count if any one of them is the same as B17 and C17

Try instead

=SUMPRODUCT(COUNTIF(Sheet2!A:O, B17:C17))
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Unless B17 and C17 are the same value, then there are 0 cells in 'Sheet 2'!A:O that are equal to BOTH B17 and C17.
It's actually not possible for any cell to be equal to 2 different values at the same time.

Can you give some more detail about exactly what you're trying to count?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
Adding to Jonmo1 comments
If you do want the volume of sales you should use SUMIFS, not COUNTIFS

M.
 

MadHatsJess

New Member
Joined
Jul 12, 2017
Messages
27

ADVERTISEMENT

Hi

Thanks everyone

Basically I have two sheets.

One sheet contains raw sales data (Example below)

Room NumProperty Name Room Type Rent Lengh of stay
1Property A Small 207
2Property A Medium 307
3Property A Small 207
4Property A Small 207
5Property A Medium 307
6Property A Medium 307
7Property BSmall 5014
8Property BSmall 5014
9Property BSmall 5014
10Property BSmall 5014
11Property BLarge 8014
12Property BLarge 8014
13Property BLarge 8014
14Property BLarge 8014
15Property BLarge 8014
16Property A Large 407
17Property A Large 407
18Property A Large 407
19Property A Large 407
20Property A Large 407
21Property A Large 407
22Property A Large 407
23Property A Large 407
24Property A Large 407
25Property A Large 407
26Property A Large 407
27Property A Large 407
28Property A Large 407
29Property A Large 407

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The other sheet has a table with set prices. I need to know how many rooms I have sold meeting certain criteria.


Property Room Type Tier 1 Price Tier 1 Limit Tier 1 SOLD Tier 2 Price Tier 2 Limit Tier 2 SOLD Lengh of stay
Property A Small 20100305 7
Property A Medium 305 406 7
Property A Large 4010 507 7
Property BSmall 503 609 14
Property BMedium 6011 7010 14
Property BLarge 7012 802 14

<colgroup><col><col><col span="2"><col><col span="2"><col><col></colgroup><tbody>
</tbody>


I use the formula below because I want to know

How many "Room Type Small" in property A I have sold at tier price 1 with the length of stay 7

=COUNTIFS(Sheet2!A:E,Sheet1!B3,Sheet2!A:E,Sheet1!C3,Sheet2!A:E,D3,Sheet2!A:E,J3)


Hope this is clear enough - let me know if not, appreciate your help!

Jess
 

MadHatsJess

New Member
Joined
Jul 12, 2017
Messages
27
Hi Thank you for the reply's

Basically I have a sheet of raw sales information.

I need to know

How many of a certain room type has Sold in a property at a certain price and for a certain tenancy length.

=COUNTIFS(Sheet2!A:E,Sheet1!B3,Sheet2!A:E,Sheet1!C3,Sheet2!A:E,D3,Sheet2!A:E,J3)

I have used the above formula but now understand it wont do the job.

The range is the same for all.

B3 is the property name
C3 is the room type
D3 is the price
J3 is the tenancy length

rows 4,5,6 etc have different types of rooms prices etc

Thanks - really appreciate the help

Jess
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Each criteria range should be a single column (the column that contains the criteria)

So assuming the match to B3 is in Column A, C3 is in column B, D3 is in C, and J3 is in D
=COUNTIFS(Sheet2!A:A,Sheet1!B3,Sheet2!B:B,Sheet1!C3,Sheet2!C:C,Sheet1!D3,Sheet2!D:D,J3)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,525
Members
414,245
Latest member
Major Aly

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
Top