Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total or Count based on multiple conditions
Hi,
I have followed the Help guidance to calculate the number of rows that contain two criteria, each from a different column but it only returns a zero value. The Help Guidance says: "Count the occurrences of multiple conditions In the following formula, whenever Excel finds "Northwind" in the range B5:B25, it then checks for the text "Western" in the same row in column C (the range C5:C25). Excel then calculates the number of rows that contain both. =SUM(IF(B5:B25="Northwind",IF(C5:C25="Western",1,0 ))) This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER." My formula is this: =SUM(IF(B2:B657="20/04/2006",IF(F2:F657="d",1,0))) This is identical to the Help formula yet it only returns a zero value. Likewise I have followed the instructions to calculatethe total value of cells that satisfy those two criteria using the fomula based on the Help example which is this: "Create a total based on multiple conditions Use the following formula to calculate the total value of cells F5:F25, where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western". =SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25)) My formula is this: =SUM(IF((B2:B657="20/04/2006")*(F2:F657="d"),G2:G657)) Again my formula is identical to the Help one yet it also only returns a zero value. Can anyone advise why this might be and what I can do to get excel to do the calculations I want? Cheers CC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total or Count based on multiple conditions
You can use sumproduct to capture multiple criteria. For a count:
=sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657=1)) To sum column G when those conditions are met: =sumproduct(--(b2:b657=date(2006,4,20)),--(f2:f657=1),g2:g657) "CC" wrote: Hi, I have followed the Help guidance to calculate the number of rows that contain two criteria, each from a different column but it only returns a zero value. The Help Guidance says: "Count the occurrences of multiple conditions In the following formula, whenever Excel finds "Northwind" in the range B5:B25, it then checks for the text "Western" in the same row in column C (the range C5:C25). Excel then calculates the number of rows that contain both. =SUM(IF(B5:B25="Northwind",IF(C5:C25="Western",1,0 ))) This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER." My formula is this: =SUM(IF(B2:B657="20/04/2006",IF(F2:F657="d",1,0))) This is identical to the Help formula yet it only returns a zero value. Likewise I have followed the instructions to calculatethe total value of cells that satisfy those two criteria using the fomula based on the Help example which is this: "Create a total based on multiple conditions Use the following formula to calculate the total value of cells F5:F25, where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western". =SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25)) My formula is this: =SUM(IF((B2:B657="20/04/2006")*(F2:F657="d"),G2:G657)) Again my formula is identical to the Help one yet it also only returns a zero value. Can anyone advise why this might be and what I can do to get excel to do the calculations I want? Cheers CC |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total or Count based on multiple conditions
if you're working with dates, you have to enter dates:
=SUM(IF((B2:B657=date(2006,4,20)*(F2:F657="d"),G2: G657)) Personally, I like =sumproduct()'s syntax (and not having to array enter the formula): =sumproduct(--(b2:b657=date(2006,4,20),--(f2:f657="d"),(g2:g657)) Adjust the ranges to match--but you can't use whole columns (like your array formula). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html CC wrote: Hi, I have followed the Help guidance to calculate the number of rows that contain two criteria, each from a different column but it only returns a zero value. The Help Guidance says: "Count the occurrences of multiple conditions In the following formula, whenever Excel finds "Northwind" in the range B5:B25, it then checks for the text "Western" in the same row in column C (the range C5:C25). Excel then calculates the number of rows that contain both. =SUM(IF(B5:B25="Northwind",IF(C5:C25="Western",1,0 ))) This is an array formula and must be entered by pressing CTRL+SHIFT+ENTER." My formula is this: =SUM(IF(B2:B657="20/04/2006",IF(F2:F657="d",1,0))) This is identical to the Help formula yet it only returns a zero value. Likewise I have followed the instructions to calculatethe total value of cells that satisfy those two criteria using the fomula based on the Help example which is this: "Create a total based on multiple conditions Use the following formula to calculate the total value of cells F5:F25, where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western". =SUM(IF((B5:B25="Northwind")*(C5:C25="Western"),F5 :F25)) My formula is this: =SUM(IF((B2:B657="20/04/2006")*(F2:F657="d"),G2:G657)) Again my formula is identical to the Help one yet it also only returns a zero value. Can anyone advise why this might be and what I can do to get excel to do the calculations I want? Cheers CC -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total or Count based on multiple conditions
Hi,
I did the sumproduct thing and it works like a dream. Thanks for that guys. CC |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Total or Count based on multiple conditions
Hi again,
I now need to sum and count from more than one date (e.g. the cumulative total of houses for sale on 20/04/2006 and 27/04/2006 and the total value of them). I have tried simply adding the second date to the sumproduct formulae above using *, OR, but that didn't work. Any suggestions? CC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
count based on multiple date criteria | Excel Worksheet Functions | |||
generate multiple rows based on cell value | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |