SumIF

[RoadKill]

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

 

[Mike H]

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]

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.

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

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

 

[RoadKill]

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.

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.

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

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

 

[Pete_UK]

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

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.

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.

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