My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Trouble with Count If formula|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Trouble with Count If formula|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope



Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_PrintTopic sp_TopicIcon
Trouble with Count If formula
Avatar
SST Admin

New Member
Members
Level 0
Forum Posts: 2
Member Since:
March 12, 2019
sp_UserOfflineSmall Offline
1
March 12, 2019 - 3:52 am
sp_Permalink sp_Print

HELP! I want to add up the total # of units and sales per month. I have the units but I can't get the dollar amounts to add up. What am I doing wrong?

Units formula: =COUNTIFS($D:$D, "Yes", $C:$C, ">="&A2, $C:$C, "<"&A3 )

Volume formula: ??

Month Sale Price Closing Date Closed Units Volume
01/01/2018 $285,000.00 01-05-18 Yes 2  
02/01/2018 $27,000.00 02-12-18 Yes 2  
03/01/2018 $228,000.00 03-16-18 Yes 1  
04/01/2018 $298,000.00 02-04-18 Yes 1  
05/01/2018 $192,000.00 06-16-18 Yes 0  
06/01/2018 $138,840.00 04-18-18 Yes 2  
07/01/18 $23,000.00 06-19-18 Yes 2  
08/01/2018 $145,000.00 08-19-18 Yes 1  
09/01/2018 $360,000.00 09-22-18 Yes 1  
10/01/2018 $179,900.00 01-23-18 Yes 1  
11/01/2018 $95,000.00 10-23-18 Yes 2  
12/01/2018 $195,000.00 11-24-18 Yes 0  
  $182,000.00 12-24-18 Yes    
  $182,000.00 07-24-18 Yes    
  $226,825.00 7/31/2018 Yes    
  $135,500.00 11-02-18 Yes    
  $189,000.00 12-15-18 Yes    
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1613
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
March 12, 2019 - 8:53 am
sp_Permalink sp_Print

Please supply the workbook so we don't have to key in the data and recreate everything.

If I enter your COUNTIFS formula into the Units column I get 0 for every answer.  Without seeing your workbook I don't see how you get something different.

I don't see how you are calculating the number of units.  The other info you've provided doesn't seem to allow such a calculation.

How exactly do you want Volume calculated?  Is Volume a $ value or a number measuring something else?

Phil

Avatar
SST Admin

New Member
Members
Level 0
Forum Posts: 2
Member Since:
March 12, 2019
sp_UserOfflineSmall Offline
3
March 12, 2019 - 11:49 pm
sp_Permalink sp_Print sp_EditHistory

Sorry I wasn't clear. This is a small part of a much larger worksheet that tracks real estate closings. I want to calculate the number of units sold per month and how much $ made each month. 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 832
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
March 13, 2019 - 8:25 pm
sp_Permalink sp_Print

It looks like you just need SUMIFS:

 

=SUMIFS($B:$B,$D:$D, "Yes", $C:$C, ">="&A2, $C:$C, "<"&A3 )

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Catalin Bombea: 1939
SunnyKow: 1432
Anders Sehlstedt: 919
Purfleet: 415
Frans Visser: 349
Hans Hallebeek: 339
David_Ng: 306
lea cohen: 248
Jessica Stewart: 219
A.Maurizio: 216
Newest Members:
Michael Denton
Nick Pascal
David Webb
Johannes Bruentrup
John Jay
Larry Silverman
David Rousseau
Melinda Heidemann
Jose Fuentes
Abigail Hernandez
Forum Stats:
Groups: 3
Forums: 25
Topics: 7072
Posts: 30993

 

Member Stats:
Guest Posters: 49
Members: 34469
Moderators: 2
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Jessica
Moderators: Velouria, Riny van Eekelen