Question description:
This user has given permission to use the problem statement for this
blog.
how can i turn my sumifs into a countifs because my sumifs works great and i also want to count the thing im summing
Solved by O. C. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
08/04/2018 - 04:29
hi
Excelchat Expert
08/04/2018 - 04:30
hello. Welcome to Got It Pro Excel
Excelchat Expert
08/04/2018 - 04:31
how may I help you today?
User
08/04/2018 - 04:32
i have a sumifs that adds up things in a column based on 2 different criteria, i want to "count" the same data so not only do i have a sum of the data but a count of the same data using the same criteria
Excelchat Expert
08/04/2018 - 04:33
the question is not clear. can you please share your spreadsheet?
User
08/04/2018 - 04:34
i can not share the sheet as its work related and im not allowed
User
08/04/2018 - 04:34
ust want to "count" the exact thing i am summing
User
08/04/2018 - 04:34
i just
Excelchat Expert
08/04/2018 - 04:34
you can share only 2 or three columns or you can put some dummy data
Excelchat Expert
08/04/2018 - 04:35
please understand that in order to provide best possible solution, I need to visualize the data
User
08/04/2018 - 04:35
=SUMIFS('[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$V:$V,'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$X:$X,"1",'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,">="&Sheet1!$C$3,'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,"<="&Sheet1!$C$4)
User
08/04/2018 - 04:36
this is my sumifs
User
08/04/2018 - 04:36
i just want to count what i am summing
Excelchat Expert
08/04/2018 - 04:37
let me check the formula
User
08/04/2018 - 04:38
i can tell you what it does if you need it
Excelchat Expert
08/04/2018 - 04:38
YES plz
User
08/04/2018 - 04:40
it sums the time in a column that meets both criteria
User
08/04/2018 - 04:40
1 criteria is a number between 1-5 and the other is a date range
Excelchat Expert
08/04/2018 - 04:42
you need to put one extra column
User
08/04/2018 - 04:42
what for?
Excelchat Expert
08/04/2018 - 04:42
and you need to put IF formula there
Excelchat Expert
08/04/2018 - 04:42
the condition must be same which you have put in SUMIFS
Excelchat Expert
08/04/2018 - 04:42
and when the condition is TRUE put 1 in cell otherwise 0
Excelchat Expert
08/04/2018 - 04:43
then simple use COUNTIF formula in column next to it
User
08/04/2018 - 04:43
well i did say i want to turn my sumifs into a countifs
Excelchat Expert
08/04/2018 - 04:43
does this make sense?
User
08/04/2018 - 04:45
no i dont want to have another column in the sheets or workbooks that the data is located in i just want to count every time my sumifs adds a number to the total
Excelchat Expert
08/04/2018 - 04:46
to use COUNTIFS
Excelchat Expert
08/04/2018 - 04:46
here is the syntax
Excelchat Expert
08/04/2018 - 04:46
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
User
08/04/2018 - 04:47
i get thae syntax for countifs i thought you might be able to turn my sumifs formula directly into a countifs because the criteria to check is exactly the same for both formula
Excelchat Expert
08/04/2018 - 04:47
I just request you to please share your workbook so that i'll do the job
Excelchat Expert
08/04/2018 - 04:48
please put some dummy data if you don't want to share original
User
08/04/2018 - 04:48
sorry im not allowed to share original
Excelchat Expert
08/04/2018 - 04:48
although we are professional and will take care of your privacy
Excelchat Expert
08/04/2018 - 04:48
give me 5 min
User
08/04/2018 - 04:48
i dont want ot loose my job for sharing
Excelchat Expert
08/04/2018 - 04:49
ok no issue... give me 5 min and i'll get back to you
User
08/04/2018 - 04:49
=SUMIFS('[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$V:$V,'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$X:$X,"1",'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,">="&Sheet1!$C$3,'[Data Ops Progress - TV P1801-P1810.xlsx]ACTV - Programmes'!$W:$W,"<="&Sheet1!$C$4)
User
08/04/2018 - 04:49
so this is my sumifs
Excelchat Expert
08/04/2018 - 04:54
please see the spreadsheet at right side
Excelchat Expert
08/04/2018 - 04:55
in CELL G2, there is SUMIFS formula and in cell H2, there is COUNTIFS formula
Excelchat Expert
08/04/2018 - 04:55
is this what you are looking for?
Excelchat Expert
08/04/2018 - 05:02
hello sir. did you check the formula?
Excelchat Expert
08/04/2018 - 05:06
please let me know if you need any further help regarding this solution
Excelchat Expert
08/04/2018 - 05:08
thanks for using Got It Pro - Excel
Excelchat Expert
08/04/2018 - 05:08
please come again
Excelchat Expert
08/04/2018 - 05:08
thank you
Excelchat Expert
08/04/2018 - 05:08
have a nice day
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
