SOLVED

Iron Contributor

# need help_how to count total with 2 criteria?

hi

I have a list of data that will run thru Dec
I like to count the total of Admin Type by Month that attend the Activities
May I know how to and not too sure if this table is best layout to do it.

Thank you

 Month Activities Contract Full Part Hour Jan 2024 Fitness Jan 2024 Workshop Jan 2024 Course Jan 2024 Fitness Feb 2024 Fitness Feb 2024 Workshop Feb 2024 Course Mar 2024 Fitness Mar 2024 Fitness Mar 2024 Workshop
4 Replies
best response confirmed by bbsin (Iron Contributor)
Solution

# Re: need help_how to count total with 2 criteria?

``=SUMPRODUCT((\$C\$3:\$C\$13=\$C19)*(\$B\$3:\$B\$13=D\$18)*(MONTH(\$A\$3:\$A\$13)=MONTH(\$B19))*(YEAR(\$A\$3:\$A\$13)=YEAR(\$B19)))``

SUMPRODUCT returns the intended result if i correctly understand what you want to do.

 Month Activities Contract Full Part Hour Jan 2024 Fitness 0 1 0 1 Jan 2024 Workshop 0 0 0 0 Jan 2024 Course 1 0 0 0 Jan 2024 Fitness 0 1 0 1 Feb 2024 Fitness 1 0 0 0 Feb 2024 Workshop 0 1 0 0 Feb 2024 Course 0 1 0 0 Mrz 2024 Fitness 0 0 1 0 Mrz 2024 Fitness 0 0 1 0 Mrz 2024 Workshop 0 0 0 0

# Re: need help_how to count total with 2 criteria?

Hi Oliver

I can't seem to get it working on my end.  not too sure why part is not right
Forgotten to mention too, that the main data 1st tab consists of few year and will keep growing, I will need to generate it by Year and end Mth for the total record in 2nd tab.

pls see the attached file TEST count, pls see tab Sum by year and Mth - I try the formula in col C4, not working.

Thank you

# Re: need help_how to count total with 2 criteria?

Hi @bbsin,

i think a closing bracket is wrong in the formula in your sample file. It should be like this:

YEAR(Data!\$B:\$B)=YEAR(\$B4)

but is like this:

YEAR(Data!\$B:\$B=YEAR(\$B4))

In my sample file i've removed the error from cell D14 of the "Data" sheet. And i've formatted the dates in the "Data" sheet as dates. They are right-aligned now like in the "Sum by year and Mth" sheet in column A.

I'd suggest to reference ranges such as

=Data!\$C\$2:\$C\$300

because this references rows 2 to 300.

On the other hand this

=Data!\$C:\$C

would be the same as this

=Data!\$C\$1:\$C\$1048576

which means it references 1048576 rows which is bad for the calculation performance in the file.

If required you can easily change

=Data!\$C\$2:\$C\$300

to e.g. this

=Data!\$C\$2:\$C\$10000

and the perfomance is still better by far than if 1048576 rows are referenced.

# Re: need help_how to count total with 2 criteria?

Thank you
1 best response

Accepted Solutions
best response confirmed by bbsin (Iron Contributor)
Solution

# Re: need help_how to count total with 2 criteria?

``=SUMPRODUCT((\$C\$3:\$C\$13=\$C19)*(\$B\$3:\$B\$13=D\$18)*(MONTH(\$A\$3:\$A\$13)=MONTH(\$B19))*(YEAR(\$A\$3:\$A\$13)=YEAR(\$B19)))``

SUMPRODUCT returns the intended result if i correctly understand what you want to do.

 Month Activities Contract Full Part Hour Jan 2024 Fitness 0 1 0 1 Jan 2024 Workshop 0 0 0 0 Jan 2024 Course 1 0 0 0 Jan 2024 Fitness 0 1 0 1 Feb 2024 Fitness 1 0 0 0 Feb 2024 Workshop 0 1 0 0 Feb 2024 Course 0 1 0 0 Mrz 2024 Fitness 0 0 1 0 Mrz 2024 Fitness 0 0 1 0 Mrz 2024 Workshop 0 0 0 0