SUMPRODUCT with multiple criteria

Toshiba Satellite a135
September 16, 2010 at 13:12:36
Specs: Microsoft Windows Vista Home Premium, 1.667 GHz / 2037 MB
ANY HELP WOULD SAVE MY HEAD!!

I have been fighting this formula for 2 days and hope someone here has a solution. Basically, I am trying to build a database tracker using this: If Column D5:D45 = HYD, column E5:E45= 5, I need to count columns G5 thru I45 if it contains R, BUT I need to count it daily based on Row 1 Columns G thru I which have dates (if it equals todays date) .

I have built a SUMPRODUCT formula that will do all the above except the daily count.


See More: SUMPRODUCT with multiple criteria

Report •


#1
September 16, 2010 at 14:15:17
OK, I'm confused.

You said:

If Column D5:D45 = HYD, column E5:E45= 5, I need to count columns G5 thru I45 if it contains R

D5:D45, E5:E45 and G5 thru I45 are not columns, they are ranges.

Do mean "for each cell in those ranges that equal those values" or do you mean "if those entire ranges equal those values"?

You also described what you want to do and then said:

I have built a SUMPRODUCT formula that will do all the above except the daily count

Since the daily count was included in your requirements "above", what all does the SUMPRODUCT formula that you built actually do?

Why not include the formula for us to see?

Perhaps it would help if you gave us some examples of inputs and expected outputs so we can see what you are trying to do.

Use the pre tags found above the Reply box to help line up your data.


Report •

#2
September 16, 2010 at 14:40:24
Sorry, this is my first post here. The product in question is at work, so as soon as I get in, I will post a better explanation and the formula I've got so far. Thanks for the quick response!
kajun6587

Report •

#3
September 16, 2010 at 23:51:33
OK, hopefully a better explanation now. Below is the formula that I have built that will count the range I want (LEAVE) if C5:C113="HYD" and G5:G113=5.

=SUMPRODUCT(--(C5:C113="HYD"),--(G5:G113=5),--ISNUMBER(MATCH(I5:I113,{"LEAVE"},0)))

My worksheet is built with:
C5:C113 has "HYD", "EGR", and "FUE"
G5:G133 has "3", "5", and "7"
I5:L113 has "R", "S", and "T" where needed (based on what day it is;some blank as well)
I Thru L our dates (Row 1),

What I need is something that will read the above formula based on TODAY(), using the dates found in I thru L (Row 1)

I hope this is a better explanantion.


Report •
Related Solutions


Ask Question