Final Excel Query :)

shaz77_2

Hi All,

This is my final query and it's bugging me. I have two tabs on my spreadsheet and I want to input some values onto tab 2 from tab 1 (Data).

I need to link two columns from the data tab (Column C which links to a team and column G which related to the achievement of targets (Yes/No). I want to count each of the achievements for each team, at the moment I am doing it manually via the following formula but there must be something faster?

=COUNTIF(Data!G871:G4645,"=Yes")

Thanks in advance

EchoLocation

Sounds like you need to match two lots of criteria? (Team and Achievment). This can be done with COUNTIFS

shaz77_2

That's correct so if Team = Liverpool and Achievement = Yes that would be one occurrence for Liverpool. I want to function to add up all for Liverpool and place beside Liverpool Cell and to do the same for other teams.

EchoLocation

=COUNTIFS(Data!C1:C10,"Liverpool",Data!G1:G10,"Yes")

Of course you can replace the criteria above with the cell references for each team and achievement on Tab 2 then just copy the formula down.

jackomdj

I do like sumifs and countifs, makes life a whole lot easier.