Help with complicated formula!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a formula that counts the number of cells that contain a keyword in one column:
=countif(a1:a10000, "keyword")

Now I need to further filter these results down by month/year data from another column (for instance, I want all of the info from the countif results, but only the ones that occurred in the month of January).

Is there anyone out there who might be able to help me, if this is at all possible? Thanks!!!!!
 
One way

=SUMPRODUCT(--(A1:A10000="keyword"),--(ISNUMBER(B1:B10000)),--(MONTH(B1:B100
00)=1))

assuming the dates are in B1:B10000, also you might want to replace the
keyword with a cell reference where you put the keyword

=SUMPRODUCT(--(A1:A10000=D2),--(ISNUMBER(B1:B10000)),--(MONTH(B1:B10000)=1))

where D2 holds the keyword

--

Regards,

Peo Sjoblom

Jon said:
I created a formula that counts the number of cells that contain a keyword in one column:
=countif(a1:a10000, "keyword")

Now I need to further filter these results down by month/year data from
Click to expand...
another column (for instance, I want all of the info from the countif
results, but only the ones that occurred in the month of January).
Is there anyone out there who might be able to help me, if this is at all
Click to expand...
possible? Thanks!!!!!
 
What do the 2 hyphens (--) signify?

Peo Sjoblom said:
One way

=SUMPRODUCT(--(A1:A10000="keyword"),--(ISNUMBER(B1:B10000)),--(MONTH(B1:B100
00)=1))

assuming the dates are in B1:B10000, also you might want to replace the
keyword with a cell reference where you put the keyword

=SUMPRODUCT(--(A1:A10000=D2),--(ISNUMBER(B1:B10000)),--(MONTH(B1:B10000)=1))

where D2 holds the keyword

--

Regards,

Peo Sjoblom

keyword
in one column:
another column (for instance, I want all of the info from the countif
results, but only the ones that occurred in the month of January). all
possible? Thanks!!!!!
Click to expand...
 
They are two minus signs. Two minuses make a plus, i.e. -(-1) is just +1, so
on first sight you might think they have no effect. Their purpose is to
force Excel to convert a true or false value into 1 or 0, as SUMPRODUCT
expects numeric parameters (rather than binary ones).
 
One way

=SUMPRODUCT(--(A1:A10000="keyword"),--(ISNUMBER(B1:B10000)),
--(MONTH(B1:B10000)=1))
Click to expand...

No need to go through B1:B10000 twice.

=SUMPRODUCT(--(A1:A10000="X"),--(ISNUMBER(1/(MONTH(B1:B10000)=1))))
 
Back
Top