Can anyone help with this? Sumproduct(sumif vs Sumproduct(Countif

[TonyTonyTony]

Kyle was kind enough to help me with the correct formula as follows:


=SUMPRODUCT(SUMIF(INDIRECT(ROW($A$1:$A$31)&"!B4:B13"),"DEANS",INDIRECT(ROW($A$1:$A$31)&"!C4:C13"))) It works perfectly.


Now instead of working with range of cells, I need to "Countif" single cells across 31 pages on one summary page. Please excuse my ignorance but I'm sure I know less about Excel than anyone that has ever posted on this forum.


In the layest of laymens terms. My same formula needs to count how many individual cells in columb B were "DEANS" and had $$$$ attached in the corresponding cell ( Column C.


Below is my attempt to explain what I'm trying to formulate.


=SUMPRODUCT(COUNTIF(INDIRECT(ROW($A$1:$A$31)&"!B4"),"DEANS",&"<0",INDIRECT(ROW($A$1:$A$31)&"C4")))


Can anyone help?

 

[Kyle McGhee]

Hi Tony,


Do you have access to Excel 2007 or higher?


=SUMPRODUCT(COUNTIFS(INDIRECT(ROW($A$1:$A$31)&"!B4:B13"),"DEANS",INDIRECT(ROW(A$A$1:$A$31)&"!C4:C13"),">0"))

 

[TonyTonyTony]

Kyle, once again you have saved me so much time and frustration. The formula works perfect. You helped more than you know. Thank you......