Excel 2010 code needed for counting based on criteria in 2 cells

[cjbrown815]

I have a spreadsheet that has a date field and a field that flags the type (CORE CHEESE)

I need to count all rows for 2010,2011,2012,2013 (Column G) that contains the words "Core Cheese" in (Column H)

so the summary would be

YEAR CORE CHEESE OTHER
2010 190 35
2011 211 41
2012 115 55

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[zelgar]

=COUNTIF(H2:H100,"=Core Cheese") will calulate how many times "Core Cheese" appears in the range H2:H100
=SUMIF(H2:H100,"=Core Cheese",G2:G100) will sum the values in Column G only those values where the value in H is "Core Cheese

 

[byundt]

If column G contains a year and column H contains CORE CHEESE and nothing else, you could use:
=COUNTIFS(G:G,2011,H:H,"CORE CHEESE") Requires Excel 2007 or later

If there might be other words in the cell besides "CORE CHEESE", then you might use:
=COUNTIFS(G:G,2011,H:H,"*CORE CHEESE*")

And if column G contains dates (rather than years), then you might use:
=COUNTIFS(G:G,">=" & "1/1/" & K2,G:G,"<" & "1/1/" & K2+1,H:H,"*CORE CHEESE*")

In this formula, cell K2 contains a year like 2010 or 2011.

 

[Loomah]

or
=sumproduct(($g$1:$g$50000=a1)*($h$1:$h$50000="core"))
where a1 contains the year

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​