**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.*