Counting cells using multiple criteria

[Randomhero180]

Hey All, I am working with a massive spreadsheet and have tried to adapt different functions to count the number of instances multiple criteria appear.

Here are the things I need

1) Range B19:E12678 I want to count if any cell has "estimate approved", "Product Ordered", "Product Staged", or "Photography Complete"

2) Range H19:H12678 This column has a date in it and I want it to count only items that fall in this month (ie Sept for this month)

3) Range V19:V12678 contains 4 different values COP, GB, FVD, NF I am looking to get a number for each category.

For example: how many items this month (sept) in the category COP, have the status update "estimate approved", "Product Ordered", "Product Staged", or "Photography Complete"

Thanks for any help!

RH

 

[VoG]

Assuming that you mean B19:B12678 try

=SUMPRODUCT(--ISNUMBER(MATCH(B19:B12678,{"estimate approved","Product Ordered","Product Staged","Photography Complete"},0)),--(MONTH(H19:H12678)=9),--(V19:V12678="COP"))

 

[Randomhero180]

No I do mean B19:E12678

can I look at that entire range? or do i need a part in the function for column b,c,d,e ?

and that code works great for the column BTW, just gotta figure out how to count the other columns as well

 

[Randomhero180]

Here is the solution I came up with that works great.

=SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!B19:E12678,{"Estimate Approved","Product Ordered","Product Staged","Photography Complete"},0))*(Sheet1!V19:V12678="COP")*(MONTH(Sheet1!H19:H12678)=9))

Thanks VoG for pointing me in the right direction!

 

[Randomhero180]

It wouldn't let me edit my last post, but I feel this is important to mention.

Switched the end my formula to this:

(MONTH(Sheet1!H19:H12678)=MONTH(TODAY())))

This way it will automatically keep up with the current month.

RH