| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif & Sumif fx ref data in multiple columns
I am using the following formulas which work fine.
=SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains FPG and Column B does not contain AER*. Also would like to use a similar formula to count values in Column B if Column A contains FPG and Column B does not contain AER*. Can someone help? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
Summation scenario:
=SUMPRODUCT((A2:A145="FPG")*("B2:B145<"AER*")*(I2 :I145)) Counting scenario: =SUMPRODUCT((A2:A145="FPG")*(B2:B145<"AER*")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hal1011" wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains FPG and Column B does not contain AER*. Also would like to use a similar formula to count values in Column B if Column A contains FPG and Column B does not contain AER*. Can someone help? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
Try...
=SUMPRODUCT(--(A2:A100="FPG"),--(B2:B100<"AER"),I2:I100) and =SUMPRODUCT(--(A2:A100="FPG"),--(B2:B100<"AER")) Hope this helps! http://www.xl-central.com In article , hal1011 wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains FPG and Column B does not contain AER*. Also would like to use a similar formula to count values in Column B if Column A contains FPG and Column B does not contain AER*. Can someone help? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
Thanks for the advice. I tried the formulas and the result is taking into
account the "FPG" part of the formulas, but not the "does not contain AER" part of the formulas. Do you have any suggestions how to fix that? "Luke M" wrote: Summation scenario: =SUMPRODUCT((A2:A145="FPG")*("B2:B145<"AER*")*(I2 :I145)) Counting scenario: =SUMPRODUCT((A2:A145="FPG")*(B2:B145<"AER*")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hal1011" wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains FPG and Column B does not contain AER*. Also would like to use a similar formula to count values in Column B if Column A contains FPG and Column B does not contain AER*. Can someone help? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
Change to:
Summation scenario: =SUMPRODUCT((A2:A145="FPG")*(ISERROR(FIND("AER",B2 :B145)))*(I2:I145)) Counting scenario: =SUMPRODUCT((A2:A145="FPG")*(ISERROR(FIND("AER",B2 :B145)))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hal1011" wrote: Thanks for the advice. I tried the formulas and the result is taking into account the "FPG" part of the formulas, but not the "does not contain AER" part of the formulas. Do you have any suggestions how to fix that? "Luke M" wrote: Summation scenario: =SUMPRODUCT((A2:A145="FPG")*("B2:B145<"AER*")*(I2 :I145)) Counting scenario: =SUMPRODUCT((A2:A145="FPG")*(B2:B145<"AER*")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hal1011" wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains FPG and Column B does not contain AER*. Also would like to use a similar formula to count values in Column B if Column A contains FPG and Column B does not contain AER*. Can someone help? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
The results of these formulas are still only taking into account the first
set of critiera (FPG) and not the second. I am basically trying to apply the same logic of a custom filter on the data in column B to include anything that does not begin with AER. Any other advice? "Domenic" wrote: Try... =SUMPRODUCT(--(A2:A100="FPG"),--(B2:B100<"AER"),I2:I100) and =SUMPRODUCT(--(A2:A100="FPG"),--(B2:B100<"AER")) Hope this helps! http://www.xl-central.com In article , hal1011 wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains âœFPGâ and Column B does not contain âœAER*â. Also would like to use a similar formula to count values in Column B if Column A contains âœFPGâ and Column B does not contain âœAER*â. Can someone help? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
The results of these formulas are still only taking into account the first
set of critiera (FPG) and not the second. I am basically trying to apply the same logic of a custom filter on the data in column B to include anything that does not begin with AER. Any other advice? "Luke M" wrote: Change to: Summation scenario: =SUMPRODUCT((A2:A145="FPG")*(ISERROR(FIND("AER",B2 :B145)))*(I2:I145)) Counting scenario: =SUMPRODUCT((A2:A145="FPG")*(ISERROR(FIND("AER",B2 :B145)))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hal1011" wrote: Thanks for the advice. I tried the formulas and the result is taking into account the "FPG" part of the formulas, but not the "does not contain AER" part of the formulas. Do you have any suggestions how to fix that? "Luke M" wrote: Summation scenario: =SUMPRODUCT((A2:A145="FPG")*("B2:B145<"AER*")*(I2 :I145)) Counting scenario: =SUMPRODUCT((A2:A145="FPG")*(B2:B145<"AER*")) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "hal1011" wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains FPG and Column B does not contain AER*. Also would like to use a similar formula to count values in Column B if Column A contains FPG and Column B does not contain AER*. Can someone help? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif fx ref data in multiple columns
In that case, try the following instead...
=SUMPRODUCT(--(A2:A100="FPG"),--(LEFT(B2:B100,3)<"AER"),I2:I100) and =SUMPRODUCT(--(A2:A100="FPG"),--(LEFT(B2:B100,3)<"AER")) Hope this helps! http://www.xl-central.com In article , hal1011 wrote: The results of these formulas are still only taking into account the first set of critiera (FPG) and not the second. I am basically trying to apply the same logic of a custom filter on the data in column B to include anything that does not begin with AER. Any other advice? "Domenic" wrote: Try... =SUMPRODUCT(--(A2:A100="FPG"),--(B2:B100<"AER"),I2:I100) and =SUMPRODUCT(--(A2:A100="FPG"),--(B2:B100<"AER")) Hope this helps! http://www.xl-central.com In article , hal1011 wrote: I am using the following formulas which work fine. =SUMIF(B2:B145,"AER*",I2:I145) =COUNTIF(B2:B145,"AER*") I would like to use similar formula to sum values in Column I, if Column A contains 裉FPG� and Column B does not contain 裉AER*�. Also would like to use a similar formula to count values in Column B if Column A contains 裉FPG� and Column B does not contain 裉AER*�. Can someone help? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF to sum multiple columns of data? | Excel Worksheet Functions | |||
| SUMPRODUCT, SUMIF, COUNTIF for multiple sheets for multiple criter | Excel Worksheet Functions | |||
| Sumif data from multiple columns | Excel Worksheet Functions | |||
| Countif & Sumif with Multiple criteria | Excel Worksheet Functions | |||
| Multiple Criteria for COUNTIF and SUMIF | Excel Worksheet Functions | |||
Linear Mode
