vlookup count?

  • Thread starter Thread starter Fluke
  • Start date Start date
F

Fluke

Hi. I am building a report sheet that looks at other workbooks for
information and gives statistics. The other workbooks are exports from an
Oracle database.

In the exported sheets, I need to check that the first column = "IWS", the
second column - a date cell - is between two dates (normally the previous
month, and the third column says "Approved", and count the rows that fall
into this category.. There's other bits to take into account in the other
workbooks, like it being Complete rather than Approved, and if it's overdue
or due within 30 days of being requested, but if I can get this formula setup
correctly I can hopefully figure the rest.

Any help is very much appreciated. I don't mind if I need more than one
formula working on each other to give me the count as they can be on hidden
sheets in the Report worksheet and I can make a front sheet that has all the
count values.
 
What you are looking for is SUMPRODUCT...

=SUMPRODUCT(--($A$2:$A$10000="AWS"),--($B$2:$B$10000>=From
Date),--($B$2:$B$10000<=To Date),--($C$2:$C$10000="Approved"))

Couple things to note...

You MUST select a range.. 2 - 10000 in the above. It will not work if you do
A:A...

All ranges must be the same size.. can't look at 2 - 500 in one of them...

the -- before the ( means return a 0 or 1 for each row I'm looking at, with
1 meaning it meets, 0 meaning it does not.

So.. for every cell that meets all of the above, the result is 1*1*1*1, or, 1.

So, the result will be the count of rows that meet the above.

If you wanted to get a sum, let's say, total Revenue for that, you would not
include the -- for the summed field. Thsi way, you'd have, say, 1*1*1*1*500,
or 500!

Then it would be like a sumif rather than a countif.
 
What you are looking for is SUMPRODUCT...

=SUMPRODUCT(--($A$2:$A$10000="AWS"),--($B$2:$B$10000>=From
Date),--($B$2:$B$10000<=To Date),--($C$2:$C$10000="Approved"))

Couple things to note...

You MUST select a range.. 2 - 10000 in the above. It will not work if you do
A:A...

All ranges must be the same size.. can't look at 2 - 500 in one of them...

the -- before the ( means return a 0 or 1 for each row I'm looking at, with
1 meaning it meets, 0 meaning it does not.

So.. for every cell that meets all of the above, the result is 1*1*1*1, or, 1.

So, the result will be the count of rows that meet the above.

If you wanted to get a sum, let's say, total Revenue for that, you would not
include the -- for the summed field. Thsi way, you'd have, say, 1*1*1*1*500,
or 500!

Then it would be like a sumif rather than a countif.
 
Worked perfectly. Thank you!!

Sean Timmons said:
What you are looking for is SUMPRODUCT...

=SUMPRODUCT(--($A$2:$A$10000="AWS"),--($B$2:$B$10000>=From
Date),--($B$2:$B$10000<=To Date),--($C$2:$C$10000="Approved"))

Couple things to note...

You MUST select a range.. 2 - 10000 in the above. It will not work if you do
A:A...

All ranges must be the same size.. can't look at 2 - 500 in one of them...

the -- before the ( means return a 0 or 1 for each row I'm looking at, with
1 meaning it meets, 0 meaning it does not.

So.. for every cell that meets all of the above, the result is 1*1*1*1, or, 1.

So, the result will be the count of rows that meet the above.

If you wanted to get a sum, let's say, total Revenue for that, you would not
include the -- for the summed field. Thsi way, you'd have, say, 1*1*1*1*500,
or 500!

Then it would be like a sumif rather than a countif.
Click to expand...
 
Worked perfectly. Thank you!!

Sean Timmons said:
What you are looking for is SUMPRODUCT...

=SUMPRODUCT(--($A$2:$A$10000="AWS"),--($B$2:$B$10000>=From
Date),--($B$2:$B$10000<=To Date),--($C$2:$C$10000="Approved"))

Couple things to note...

You MUST select a range.. 2 - 10000 in the above. It will not work if you do
A:A...

All ranges must be the same size.. can't look at 2 - 500 in one of them...

the -- before the ( means return a 0 or 1 for each row I'm looking at, with
1 meaning it meets, 0 meaning it does not.

So.. for every cell that meets all of the above, the result is 1*1*1*1, or, 1.

So, the result will be the count of rows that meet the above.

If you wanted to get a sum, let's say, total Revenue for that, you would not
include the -- for the summed field. Thsi way, you'd have, say, 1*1*1*1*500,
or 500!

Then it would be like a sumif rather than a countif.
Click to expand...
 
Back
Top