CODEWITHSUNDEEP
excel
Ashley
Ashley

Reputation: 155

Finding Average amount in excel for two values

enter image description hereI have a question regarding an excel sheet. I'm trying to find how many times something occurs on a specific date. For example:

Date                    User
1/1/2014 0:00           Michael Albanese
1/1/2014 0:00           Linus Nienstadt
1/1/2014 0:01           Omar Albanese

I want to find how many times Michael Albanese occurred in 2014

I've tried the following, but it returns an error.

=COUNTIFS(F2:F482240,"=Michael Albanese",E2:E482240,"=1/1/2014")

... it then says one, but there's at least three

Upvotes: 2

Views: 61

Answers (4)

Scott Craner
Scott Craner

Reputation: 152505

Use SUMPRODUCT():

=SUMPRODUCT((YEAR($A$2:$A$4)=2014)*($B$2:$B$4="Michael Albanese"))

If your dates are text that looks like dates then use this:

=SUMPRODUCT((ISNUMBER(SEARCH(2014,$A$2:$A$4)))*($B$2:$B$4="Michael Albanese"))

Upvotes: 1

Meuryc
Meuryc

Reputation: 1

Findwindow is correct. Here is a quick example.

=COUNTIFS(A2:A10,"<1/1/2015",A2:A10,">12/31/2013",B2:B10,"=Michael Albanese")

Upvotes: 0

Forward Ed
Forward Ed

Reputation: 9874

=SUMPRODUCT((YEAR(L2:L4)=2014)*(M2:M4="Michael Albanese"))

L2:L4 is your date stored in an Excel date serial format but can be formatted as text.

M2:M4 is your list of names

If your date time is stored as text you could try

=SUMPRODUCT((MID(L2:L4,FIND(" ",L2:L4)-4,4)*1=2014)*(M2:M4="Michael Albanese"))

Upvotes: 0

user4039065
user4039065

Reputation:

The COUNTIFS function may be more efficient than the SUMPRODUCT function in this case.

=COUNTIFS(F:F, "Michael Albanese", E:E,">="&DATE(2014, 1, 1), E:E,"<"&DATE(2015, 1, 1))

Upvotes: 1

Related Questions