Hi,
I have the following data in cells c3:e147
<tbody>
</tbody>I want to be able to sum the data in column E if the ST code = 00 or 01 and the MJR code is between 2AAAA - 7ZZZZ.
I have the following sumifs formula working if I want to sum all the data when the MJR code is between 2 - 7, like this:
and I have the data summed if the value in column C is a constant, like this:
But I don't know how to find the total of E when the store cd = 00 or 01 and the MJR range is between 2 - 7.
Please help. I know I'm close, but just can't figure out the way to combine it all.
Thanks
Deb
I have the following data in cells c3:e147
| st | mjr | sales |
| 02 | 10ZZZ | $ 699 |
| 03 | 10ZZZ | $ 899 |
| 08 | 10ZZZ | $ 1,319 |
| 01 | 10ZZZ | $ 1,334 |
| 08 | 1ARUG | $ (14) |
| 03 | 1ARUG | $ 36 |
| 00 | 1ARUG | $ 198 |
| 00 | 1CARP | $ 253 |
| 03 | 1CARP | $ 3,366 |
| 02 | 1CARP | $ 4,874 |
| 01 | 1CARP | $ 5,070 |
| 08 | 1CARP | $ 6,580 |
| 00 | 1CUSH | $ 64 |
| 03 | 1CUSH | $ 582 |
| 02 | 1CUSH | $ 847 |
| 01 | 1CUSH | $ 1,714 |
| 08 | 1CUSH | $ 2,226 |
| 03 | 1SUND | $ 65 |
| 02 | 1SUND | $ 94 |
| 08 | 1SUND | $ 194 |
| 01 | 1SUND | $ 812 |
| 08 | 1TILE | $ 198 |
| 01 | 1TILE | $ 1,315 |
| 08 | 1UNDE | $ (325) |
| 03 | 1UNDE | $ 297 |
| 00 | 1VNYL | $ 108 |
| 03 | 1VNYL | $ 481 |
| 01 | 1VNYL | $ 3,486 |
| 03 | 1WARR | $ - |
| 08 | 1WARR | $ - |
| 01 | 1WARR | $ 134 |
| 01 | 1WIND | $ 2,583 |
| 02 | 1WOOD | $ 601 |
| 08 | 1WOOD | $ 1,240 |
| 01 | 1WOOD | $ 4,222 |
| 02 | 1ZLAB | $ 1,707 |
| 03 | 1ZLAB | $ 2,261 |
| 01 | 1ZLAB | $ 2,605 |
| 08 | 1ZLAB | $ 3,920 |
| 00 | 2BEDD | $ 1,938 |
| 02 | 2BEDD | $ 3,541 |
| 08 | 2BEDD | $ 7,743 |
| 03 | 2BEDD | $ 8,808 |
| 01 | 2BEDD | $ 18,997 |
| 02 | 2BEDR | $ 1,164 |
| 03 | 2BEDR | $ 1,292 |
| 01 | 2BEDR | $ 1,427 |
| 08 | 2BEDR | $ 2,300 |
| 03 | 2JUVE | $ 139 |
| 02 | 2JUVE | $ 611 |
| 01 | 2JUVE | $ 1,473 |
| 08 | 2JUVE | $ 2,740 |
| 00 | 3LIVI | $ 2,055 |
| 01 | 3LIVI | $ 4,517 |
| 02 | 3LIVI | $ 6,404 |
| 03 | 3LIVI | $ 7,777 |
| 08 | 3LIVI | $ 13,104 |
| 02 | 3MOTI | $ 1,350 |
| 08 | 3MOTI | $ 2,776 |
| 00 | 3MOTI | $ 3,003 |
| 03 | 3MOTI | $ 3,462 |
| 01 | 3MOTI | $ 4,263 |
| 08 | 3OCCA | $ - |
| 03 | 3OCCA | $ 283 |
| 02 | 3OCCA | $ 330 |
| 01 | 3OCCA | $ 1,595 |
| 00 | 3RECL | $ 648 |
| 02 | 3RECL | $ 4,245 |
| 01 | 3RECL | $ 5,347 |
| 03 | 3RECL | $ 10,928 |
| 08 | 3RECL | $ 12,262 |
| 02 | 3SECT | $ 1,200 |
| 03 | 3SECT | $ 4,246 |
| 01 | 3SECT | $ 4,360 |
| 08 | 3SECT | $ 10,311 |
| 01 | 3SLEE | $ 700 |
| 02 | 3SLEE | $ 1,399 |
| 03 | 3SLEE | $ 1,974 |
| 08 | 3SLEE | $ 3,905 |
| 03 | 4DESK | $ 240 |
| 02 | 4ENTE | $ 348 |
| 01 | 4ENTE | $ 789 |
| 08 | 4ENTE | $ 874 |
| 03 | 4MISC | $ (65) |
| 01 | 4MISC | $ 184 |
| 08 | 4MISC | $ 800 |
| 02 | 4MISC | $ 937 |
| 08 | 4TABL | $ 500 |
| 03 | 4TABL | $ 919 |
| 02 | 4TABL | $ 929 |
| 01 | 4TABL | $ 1,400 |
| 03 | 4WALL | $ (400) |
| 08 | 4WALL | $ 581 |
| 03 | 5DINE | $ (132) |
| 08 | 5DINE | $ 200 |
| 01 | 5DINI | $ (838) |
| 03 | 5DINI | $ 615 |
| 02 | 5DINI | $ 776 |
| 08 | 5DINI | $ 4,505 |
| 02 | 6LAMP | $ 240 |
| 03 | 6LAMP | $ 566 |
| 01 | 6LAMP | $ 580 |
| 03 | 6ROOM | $ 12 |
| 02 | 6ROOM | $ 48 |
| 01 | 6ROOM | $ 199 |
| 08 | 6ROOM | $ 540 |
| 01 | 6SILK | $ 30 |
| 08 | 6WALL | $ (189) |
| 03 | 6WALL | $ 20 |
| 01 | 6WALL | $ 198 |
| 00 | 7MISC | $ (269) |
| 02 | 7MISC | $ 715 |
| 01 | 7MISC | $ 1,686 |
| 03 | 7MISC | $ 1,983 |
| 08 | 7MISC | $ 3,288 |
| 03 | 8DISH | $ 430 |
| 02 | 8DISH | $ 729 |
| 01 | 8DISH | $ 1,977 |
| 03 | 8DRYE | $ 871 |
| 01 | 8DRYE | $ 2,782 |
| 01 | 8FREE | $ 598 |
| 03 | 8FREE | $ 1,105 |
| 01 | 8MICR | $ 683 |
| 02 | 8MISC | $ 140 |
| 03 | 8MISC | $ 167 |
| 01 | 8MISC | $ 1,156 |
| 03 | 8RANG | $ 1,025 |
| 02 | 8RANG | $ 2,097 |
| 01 | 8RANG | $ 4,420 |
| 02 | 8REFR | $ 850 |
| 03 | 8REFR | $ 1,520 |
| 01 | 8REFR | $ 4,616 |
| 03 | 8WASH | $ 1,264 |
| 01 | 8WASH | $ 3,109 |
| 02 | 9MISC | $ 120 |
| 03 | 9STER | $ 100 |
| 01 | 9STER | $ 235 |
| 08 | 9TELE | $ 850 |
| 02 | 9TELE | $ 1,400 |
| 01 | 9TELE | $ 1,649 |
| 03 | 9VCR | $ - |
| 03 | 9ZPAR | $ 24 |
| 03 | SERVI | $ (125) |
| 01 | SERVI | $ 60 |
<tbody>
</tbody>
I have the following sumifs formula working if I want to sum all the data when the MJR code is between 2 - 7, like this:
=SUMIFS(E3:E1000,D3:D1000,">2*",D3:D1000,"<8a*")
and I have the data summed if the value in column C is a constant, like this:
=SUM(IF(ALL!$D$4:$D$445=A6,IF(ALL!$C$4:$C$445="00",ALL!$E$4:$E$445,0),0))+SUM(IF(ALL!$D$4:$D$445=A6,IF(ALL!$C$4:$C$445="01",ALL!$E$4:$E$445,0),0))
But I don't know how to find the total of E when the store cd = 00 or 01 and the MJR range is between 2 - 7.
Please help. I know I'm close, but just can't figure out the way to combine it all.
Thanks
Deb
