Help with complicated formula!

[Jon]

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!!!!!

 

[Peo Sjoblom]

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

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!!!!!

 

[Paul Lautman]

What do the 2 hyphens (--) signify?

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!!!!!

 

[Paul]

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).

 

[Paul Lautman]

Thanks Paul,
Would you mind answering another couple of questions that I have?

 

[Harlan Grove]

One way

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

No need to go through B1:B10000 twice.

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