SumIF
 

Okay, I am a total rookie with SumIFs. I want workbook March.xls to link
whatever is in cell B1 of March.xls to link to QTD.xls workbook column
A1:A100 and count the number of instances that are over 20% as well as those
over 30%.

Does this make sense?

Thanks

SumIF
 

Hi,

I think I understand so try this in march.xls

=SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0))
Enter with Ctrl+Shift+Enter

It will divide each cell in QTD.xls by march.xls b1 and count to number of
time the result is = 20%. Change 0.2 to 0.3 for 30%

Mike

"RoadKill" wrote:

Okay, I am a total rookie with SumIFs. I want workbook March.xls to link
whatever is in cell B1 of March.xls to link to QTD.xls workbook column
A1:A100 and count the number of instances that are over 20% as well as those
over 30%.

Does this make sense?

Thanks

SumIF
 

Okay, let me clarify. It sounds like this is close. If cell B1 in March
equals "George" then I want to count the number of Georges in Column A of QTD
that have a score of 20-29.99% in Column B of QTD as well as the number of
scores above 30%.

Sorry for the confusion.

"Mike H" wrote:

Hi,

I think I understand so try this in march.xls

=SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0))
Enter with Ctrl+Shift+Enter

It will divide each cell in QTD.xls by march.xls b1 and count to number of
time the result is = 20%. Change 0.2 to 0.3 for 30%

Mike

"RoadKill" wrote:

Okay, I am a total rookie with SumIFs. I want workbook March.xls to link
whatever is in cell B1 of March.xls to link to QTD.xls workbook column
A1:A100 and count the number of instances that are over 20% as well as those
over 30%.

Does this make sense?

Thanks

SumIF
 

Thinking it over, let's simplify it to a single workbook.

When the contents of H1 (George) matches any instances of it in column
A1:A100, I want it to count the number of scores of 20-29.99% in B1:B100.


"RoadKill" wrote:

Okay, let me clarify. It sounds like this is close. If cell B1 in March
equals "George" then I want to count the number of Georges in Column A of QTD
that have a score of 20-29.99% in Column B of QTD as well as the number of
scores above 30%.

Sorry for the confusion.

"Mike H" wrote:

Hi,

I think I understand so try this in march.xls

=SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0))
Enter with Ctrl+Shift+Enter

It will divide each cell in QTD.xls by march.xls b1 and count to number of
time the result is = 20%. Change 0.2 to 0.3 for 30%

Mike

"RoadKill" wrote:

Okay, I am a total rookie with SumIFs. I want workbook March.xls to link
whatever is in cell B1 of March.xls to link to QTD.xls workbook column
A1:A100 and count the number of instances that are over 20% as well as those
over 30%.

Does this make sense?

Thanks

SumIF
 

You have more than one criteria, so you can't use SUMIF - try this
instead:

=SUMPRODUCT((B$1:B$100=0.2)*(B$1:B$100<0.3)*(A$1: A$100="George"))

Or, if you put George in cell D1 (and other names below):

=SUMPRODUCT((B$1:B$100=0.2)*(B$1:B$100<0.3)*(A$1: A$100=D1))

and copy down to cover your names in column D.

Hope this helps.

Pete

On Mar 14, 9:53*pm, RoadKill
wrote:
Thinking it over, let's simplify it to a single workbook.

When the contents of H1 (George) matches any instances of it in column
A1:A100, I want it to count the number of scores of 20-29.99% in B1:B100.



"RoadKill" wrote:
Okay, let me clarify. It sounds like this is close. If cell B1 in March
equals "George" then I want to count the number of Georges in Column A of QTD
that have a score of 20-29.99% in Column B of QTD as well as the number of
scores above 30%.

Sorry for the confusion.

"Mike H" wrote:

Hi,

I think I understand so try this in march.xls

=SUM(IF(([QTD.xls]Sheet1!$A$1:$A$100/B1=0.2), 1, 0))
Enter with Ctrl+Shift+Enter

It will divide each cell in QTD.xls by march.xls b1 and count to number of
time the result is = 20%. Change 0.2 to 0.3 for 30%

Mike

"RoadKill" wrote:

Okay, I am a total rookie with SumIFs. I want workbook March.xls to link
whatever is in cell B1 of March.xls to link to QTD.xls workbook column
A1:A100 and count the number of instances that are over 20% as well as those
over 30%.

Does this make sense?

Thanks- Hide quoted text -

- Show quoted text -