Formula Assistance

[Neffy]

Hello,

I need some help regarding formula-ing this.
It goes something like this !
Hopefully i made sense, if not, let me know and i'll attempt to clarify it.

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Department[/TD]
[TD]Seconds[/TD]
[TD]Purchased[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]18 July 2018[/TD]
[TD]Home[/TD]
[TD]500[/TD]
[TD]1[/TD]
[TD]as[/TD]
[TD]fs[/TD]
[/TR]
[TR]
[TD]19 August 2018[/TD]
[TD]Phone[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]asf[/TD]
[TD]sf[/TD]
[/TR]
[TR]
[TD]19 August 2018[/TD]
[TD]Home[/TD]
[TD]500[/TD]
[TD]1[/TD]
[TD]s[/TD]
[TD]s[/TD]
[/TR]
[TR]
[TD]20 August 2018[/TD]
[TD]PC[/TD]
[TD]700[/TD]
[TD]4[/TD]
[TD]h[/TD]
[TD]gs[/TD]
[/TR]
[TR]
[TD]20 August 2018[/TD]
[TD]PC[/TD]
[TD]857[/TD]
[TD]2[/TD]
[TD]d[/TD]
[TD]s[/TD]
[/TR]
[TR]
[TD]24 August 2018[/TD]
[TD]TV[/TD]
[TD]500[/TD]
[TD]2[/TD]
[TD]s[/TD]
[TD]s[/TD]
[/TR]
[TR]
[TD]8 September 2018[/TD]
[TD]Home[/TD]
[TD]676[/TD]
[TD]5[/TD]
[TD]s[/TD]
[TD]s[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]Contact[/TD]
[TD]Average Seconds[/TD]
[TD]Purchased[/TD]
[/TR]
[TR]
[TD]Home[/TD]
[TD]Count how many times Home Appears on the Month (August) so in this case 1 times since it should exclude other months such as July and September[/TD]
[TD]Avg of seconds when Home Appears in the month August (so excluding July September results)[/TD]
[TD]Add of all the purchases in the Purchases Tab in the month of August[/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TV[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Would that be possible ??

Thank you !

 

[Joe4]

You should be able to do this with SUMIF and COUNTIF formula.
SUMIF will sum values meeting a certain criteria. See: https://www.techonthenet.com/excel/formulas/sumif.php
COUNTIF will count values meeting a certain criteria. See: https://www.techonthenet.com/excel/formulas/countif.php

For an average, you can just divide the results of a SUMIF by the results of a COUNTIF.

 

[Neffy]

I understood the basics but for the COUNTIF formula
but for table 2

B2 cell
how do i get the cell to count if it fulfills 2 conditions?
Count how many "Home" in Column B in "August" in Column A?

 

[Joe4]

There are also COUNTIFS and SUMIFS functions for multiple conditions!
https://www.techonthenet.com/excel/formulas/sumifs.php
https://www.techonthenet.com/excel/formulas/countifs.php