SOLVED

need help_how to count total with 2 criteria?

Iron Contributor

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

MonthActivitiesContractFullPartHour
Jan 2024Fitness    
Jan 2024Workshop     
Jan 2024Course    
Jan 2024Fitness    
Feb 2024Fitness    
Feb 2024Workshop     
Feb 2024Course    
Mar 2024Fitness    
Mar 2024Fitness    
Mar 2024Workshop     
4 Replies
best response confirmed by bbsin (Iron Contributor)
Solution

@bbsin 

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

 

MonthActivitiesContractFullPartHour
Jan 2024Fitness0101
Jan 2024Workshop 0000
Jan 2024Course1000
Jan 2024Fitness0101
Feb 2024Fitness1000
Feb 2024Workshop 0100
Feb 2024Course0100
Mrz 2024Fitness0010
Mrz 2024Fitness0010
Mrz 2024Workshop 0000

 

 

@OliverScheurich 

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

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.

Thank you
1 best response

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

@bbsin 

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

 

MonthActivitiesContractFullPartHour
Jan 2024Fitness0101
Jan 2024Workshop 0000
Jan 2024Course1000
Jan 2024Fitness0101
Feb 2024Fitness1000
Feb 2024Workshop 0100
Feb 2024Course0100
Mrz 2024Fitness0010
Mrz 2024Fitness0010
Mrz 2024Workshop 0000

 

 

View solution in original post